itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: create a monthly chart of a measure with 2 criteria

It is quite simple to create a monthly chart when my measure has a simple calculation but if my measure has 2 criteria to calculate the value, the chart is not displaying correctly. To do so, I have to add a monthly filter in my calculation. Let’s take an example. I have this simple measure:

COUNT('table'[argument])

power bi

Creating the monthly chart, it will look like that:

power bi

Now I have this measure with 2 conditions:

COUNTROWS(FILTER(ALLSELECTED('table'),'table'[argument1]="xxx" && 'table'[argument2]="xxx"))

power bi

Using the same chart, I will replace in the “values” field, “measure” by “measure 2”, and I get this:

power bi

Every month, the value is the same, of course, it may happen but in my example, it should not. The way to remediate, it is to add a monthly filter in my calculation for my “measure 2”:

CALCULATE(COUNTROWS(FILTER(ALLSELECTED('table'),'table'[argument1]="xxx" && 'table'[argument]="xxx2")),FILTER(ALLSELECTED('table'),'table'[argument3]=MAX('table'[argument3])))

power bi

And now, the chart is showing correctly.

power bi

Interesting Topics