Mostrando postagens com marcador Power BI. Mostrar todas as postagens
Mostrando postagens com marcador Power BI. Mostrar todas as postagens

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] )

Power Query M language x SQL statements

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)

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)


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.

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

VAR salesMeasure = [Total Sales]



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

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

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


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

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

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

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])

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])



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


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

    [YTD] + [BOY CY],

    [Sales Volume]


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

Page Navigation buttons

Active = 


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.

Criar tabela calendário

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 = 



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

            [Sales Vol]






Didnot work

Sales Vol 3M Avg. = 

VAR NumofMonths = 3

VAR RollingSum = 


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





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

Sales Rolling Averag 2 = 



        [Sales Vol],

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



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

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




dynamic parameter (editar parametro a partir de um filtro)

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.

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

Como achar o PowerShell:

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

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 =


    SELECTEDVALUE ( DimMeasure[Measure Name] ),

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

    "Total Active Administrations", [Total Active Administrations]


misturar meses e quarters:

using measures as columns using calculation group

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

Botão Calculation Group não aparece

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

Calculation Groups - 3 usos

Creating Help Popup

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