28 de março de 2024

Como fazer isso Power BI

Minhas anotações de Power BI, sobre como fazer métricas específicas, cálculos, formatar tipos de gráfico, resolver problemas de interação nos dashboards e outras dicas que podem ser úteis





Line chart shows gap when there’s no data

(instead of connecting)

Measure = IF ( [Total Sales] = 0, BLANK (), [Total Sales] )

https://community.fabric.microsoft.com/t5/Desktop/Line-graph-Line-continues-for-months-with-no-data/m-p/92816

https://stackoverflow.com/questions/55495656/show-0-value-on-line-chart-in-power-bi-for-missing-data



Power Query M language x SQL statements

https://blog.crossjoin.co.uk/2017/06/11/query-folding-and-writing-your-own-sql-queries-in-power-querypower-biexcel-get-transform/

https://forum.enterprisedna.co/t/advance-query-editor-or-create-view-in-sql/20431


Transformar campo de texto em data

I have a text field that contains year and month only (ex '201801'). How do i create a date field using this text field.


Column = DATE(INT(LEFT([Column1],4)),INT(RIGHT([Column1],2)),1)


https://community.fabric.microsoft.com/t5/Desktop/How-do-I-create-a-date-field-using-a-text-field-that-has-year/m-p/638531




Calculations and field parameters


Unfortunately, you cannot use Field Parameters directly in calculations.

You'll have to have something like this:

FP PrevYear =

  var pyDates = DateAdd('Datetbl'[Date], -1, YEAR)

  return SWITCH( MIN(FP[FP Fields]),

    NAMEOF([Sales Measure]), CALCULATE([Sales Measure], pyDates),

    NAMEOF([Margin Measure]), CALCULATE([Margin Measure], pyDates),

    NAMEOF([Cost Measure]), CALCULATE([Cost Measure], pyDates)

  )

https://stackoverflow.com/questions/77557085/how-to-use-calculate-with-field-parameter



Power BI DAX How to Calculate and Filter Based on Dates

https://plainlyresults.com/blog/power-bi-dax-how-to-calculate-and-filter-based-on-dates/



DAX with Dates: The Power Plays

Here are some helpful rules to keep in mind when working with DAX measure calculations.

  • Calculations works against data source and not against the visual (such as a table) you're adding them to.
  • Measures work independently of one another. This means removing a measure from a table won't impact the calculations of the other measures in the table.
  • Filters apply first, then calculations. Unlike Excel, DAX doesn't work on individual cells within tables, but instead works on tables and columns. This means that you'll need to leverage a neat trick with harvesting parameters to reference the value of a cell equivalent in Power BI to create these calculations.


DAX measures work more efficiently than DAX columns because they minimize model size by eliminating the need to add new calculated columns. 

https://www.codemag.com/Article/2111051/DAX-with-Dates-The-Power-Plays




Show numbers in Thousands or Millions in same column with the new Dynamic formatting in Power BI

VAR salesMeasure = [Total Sales]

return

SWITCH(TRUE(),

salesMeasure-1000000000000 && salesMeasure< -1000000000, "#,,,.0B",

salesMeasure-1000000000 && salesMeasure< -1000000, "#,,.0M",

salesMeasure> -1000000 && salesMeasure< -1000,"#,.0K",

salesMeasure<100000,"0",

salesMeasure<1000000, "#,.0K", 

salesMeasure<1000000000, "#,,.0M",

salesMeasure>=1000000000, "#,,,.0B")


https://www.youtube.com/watch?v=ROyVkQ9vTjc&ab_channel=Curbal

https://curbal.com/curbal-learning-portal/show-data-labels-in-k-or-m-in-the-same-column-dynamic-formatting-in-power-bi


Use DAX to Show Values in any Way You Like! Dynamic Format Strings in Power BI

  • create a table:


Unit Unit Format Sort Order

None #,##0 1

Thousands #,##0,. 2

Millions #,##0,,.0 3

Billions #,##0,,,.00 4


  • create a slicer with the “unit” field
  • format the field with “Dynamic”
  • SELECTEDVALUE(Units[Unit Format])



https://www.youtube.com/watch?v=a3D7oXYjM9k&ab_channel=HowtoPowerBI




Measure = VAR CY2=SUMX(FILTER(ALL('Table'),'Table'[subject]="CY"&&'Table'[date]=SELECTEDVALUE('Table'[date])),'Table'[VALUE])

VAR PY2=SUMX(FILTER(ALL('Table'),'Table'[subject]="PY"&&'Table'[date]=SELECTEDVALUE('Table'[date])),'Table'[VALUE])

RETURN

_PY-_1




CY = 

-- Current Year

-- If year is current, Sum of YTD (Year to date) and BOY (Balance of Year)

-- If year is other, sum of sales for all months


IF(

    YEAR(MAX('24MonthOutlookVolume'[Date])) = YEAR(TODAY()),

    [YTD] + [BOY CY],

    [Sales Volume]

)



How to Add a “None” Option to Power BI Field Parameters

https://barneylawrence.com/2022/08/17/how-to-add-a-none-option-to-power-bi-field-parameters/


Chart Legend = {

    ("None", "None", 0),

    ("ChannelName", NAMEOF('Channel'[ChannelName]), 1),

    ("ContinentName", NAMEOF('Geography'[ContinentName]), 2),

    ("StoreType", NAMEOF('Stores'[StoreType]), 3),

    ("ProductCategory", NAMEOF('ProductCategory'[ProductCategory]), 4)

}

This option comes with the warning that it’s in no way a supported approach. We’re basically breaking the parameter by making it look for a field that doesn’t exist and there could be consequences I’m not aware of. If you fel this is too much of a risk then stick with option 1.




Organizing - Create a Measures table

https://www.youtube.com/watch?v=pfUXvmcDWeo&ab_channel=phData


https://www.youtube.com/watch?v=cGnWPua-cmo&ab_channel=HowtoPowerBI

https://community.fabric.microsoft.com/t5/Desktop/PowerBI-Desktop-duplicate-a-table-including-all-DAX/td-p/3453866




Page Navigation buttons

https://community.fabric.microsoft.com/t5/Desktop/Disable-page-navigation-button/td-p/1288229


Active = 

SWITCH(
SELECTEDVALUE(’



Ordenar nome dos meses


Go to the database tab, select month name column from your calendar table.  Select the modeling tab and then "Sort by Column" and select your month number column.  Month name should now appear in the correct order.


https://community.fabric.microsoft.com/t5/Desktop/Month-Name-in-sorting-order/m-p/3163763


https://community.fabric.microsoft.com/t5/Desktop/Two-date-slicers-for-start-and-end-date/td-p/2968815




Criar tabela calendário

https://www.youtube.com/watch?v=UBaW5jwLWsM&t=0s


https://github.com/alisonpezzott/powerbi/blob/main/power-query-m/fxGeraCalendarioAlisonV3




calculate last 12 month rolling average


Sales Vol 3M Avg. = 

VAR NumofMonths = 3

VAR LastCurrentDate = MAX('GapAnalysisWKFCSF'[Date])

VAR Period = DATESINPERIOD('Date Table'[Date],LastCurrentDate,-NumofMonths,MONTH)

VAR Result = 

    CALCULATE(

        AVERAGEX(

            VALUES('Date Table'[Month Name Short]),

            [Sales Vol]

            ),

            Period

        )

RETURN

       Result


Didnot work

https://www.youtube.com/watch?v=KofI18ZCPOc&ab_channel=LearnAtCloudAnalytics




Sales Vol 3M Avg. = 

VAR NumofMonths = 3

VAR RollingSum = 

    CALCULATE(

        SUM(GapAnalysisWKFCSF[Sales Vol&ZCALMTH&-&ZCALMTH2&]),

        DATESINPERIOD(GapAnalysisWKFCSF[Date],LASTDATE(GapAnalysisWKFCSF[Date]),-NumofMonths,MONTH)

    )

    RETURN

    RollingSum/NumofMonths


Worked but with pure date (not date hierarchy) and only from dates in the original source (cannot use date from date table)

https://www.youtube.com/watch?v=zYFbr_GsahI&ab_channel=EssentialExcel





Sales Rolling Averag 2 = 

DIVIDE (

    CALCULATE (

        [Sales Vol],

        DATESINPERIOD ( 'Date Table'[Date], MAX (GapAnalysisWKFCSF[Date] ), -3, MONTH )

    ),

    CALCULATE (

        DISTINCTCOUNT ( 'Date Table'[Month Name] ),

        DATESINPERIOD ( 'Date Table'[Date], MAX ( 'Date Table'[Date] ), -3, MONTH )

    )

)

WORKED

https://community.fabric.microsoft.com/t5/Desktop/Last-3-months-average/td-p/1569825





dynamic parameter (editar parametro a partir de um filtro)

https://www.youtube.com/watch?v=iiNDq2VrZPY&ab_channel=GuyinaCube


How to Use & Update Parameters in the Power BI Service

Limitation of parameters in PBI:

• Reports can only be published from Power BI desktop, other publishing processes, such as SharePoint/One Drive will not work.

• To modify the parameters in the service, the developer will need admin rights to the gateway on which the source system resides.

https://www.designmind.com/blog/business-intelligence/power-bi-parameters-how-to-use-parameters





Export Measures de um arquivo .pbix

  • Abrir arquivo .pbix no Power BI Desktop
  • Clicar em Save As
  • Clicar em Browse this device
  • Escolher tipo do arquivo como .pbit
  • Rodar script do PowerShell para extrair as measures


*comigo não funcionou 100%, não veio o conteúdo completo das métricas com mais de uma linha

https://azureops.org/articles/export-measures-from-power-bi/


Como achar o PowerShell:

https://learn.microsoft.com/pt-br/powershell/scripting/windows-powershell/ise/introducing-the-windows-powershell-ise?view=powershell-7.4


Uma outra opção mais completa, não testei:

https://www.sqlbi.com/articles/data-model-size-with-vertipaq-analyzer/


mostrar mais de uma dimensão na Matrix

  • usar uma tabela desconectada das outras só com o nome e o DAX das métricas


Switch Measure =

SWITCH (

    SELECTEDVALUE ( DimMeasure[Measure Name] ),

    "Total Autobooked Invoices %", [Total Autobooked Invoices %],

    "Total Active Administrations", [Total Active Administrations]

)


https://community.fabric.microsoft.com/t5/Desktop/Adding-another-dimension-to-matrix-table-with-values-as-row/td-p/2522718



misturar meses e quarters:

https://www.youtube.com/watch?v=gv4kCt7vK7U


https://community.fabric.microsoft.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591/page/3



using measures as columns using calculation group

https://www.youtube.com/watch?v=90X5_tISxIY



to create a Time Intelligence table which contains blocks of rows per period. Some example time-periods might be:

  • Current Year
  • Prior Year
  • Last 2 Months
  • Last 3 Weeks
  • Last 28 Days
  • Year to Date


https://dax.tips/2019/10/09/row-based-time-intelligence/





Botão Calculation Group não aparece


  • Abre as preferências
  • Preview Features
  • Habilitar a opção “Model explorer and Calculation group authoring”


https://community.fabric.microsoft.com/t5/Desktop/Unable-to-find-calculation-group-option-in-power-BI-Desktop/td-p/3696127



Calculation Groups - 3 usos

https://www.youtube.com/watch?v=ZOOdmUCO3m8



Creating Help Popup

https://www.youtube.com/watch?v=JZ0FwDr9SXU



Bookmark that dont mess up with filters

Normalmente os bookmarks salvam junto a seleção de filtros, prejudicando a interação desejada pra ele (criar um popup ou alguma navegação interna). Para desabilitar isso e permitir que o bookmark não carregue as opções de filtro:

  • abra o painel de bookmarks (View > Bookmarks)
  • clique nas opções do bookmark (botão direito ou clique no ícone de três pontinhos)
  • desmarque a opção “Data”
  • pronto. Agora habilitar ou desabilitar o popup não altera os filtros selecionados no dashboard