Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a report that has been published since 8/30/21. It was working properly up until 9/1 at least (last time I interacted with it). When I look at the report in Power BI service or website where it is embedded as of right now, many of the visuals are not working.
When I click "See Details" I get an error message as follows:
MdxScript(Model) (118, 9) Calculation error in measure 'Sales Data_Standard'[Static_SelectedMeasureFilteredMTDLY]: A function 'CONTAINSROW' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
This visual and all others displaying this error are working fine in Desktop. I have looked at the Dax for the measure in question and it does not use CONTAINSROW. The DAX for this measure is:
Does anybody know why this error is happening on Power BI service and not in Desktop? It seems strange to me that it was working fine, nothing was changed and now it is broken.
Thanks!
Solved! Go to Solution.
The IN operator is executing CONTAINSROW in the background. The second SWITCH is functioning as a filter, so that's what's driving the error.
There are a lot of options for rewriting this. Here is one:
Static_SelectedMeasureUnfiltered =
CALCULATE(
SWITCH(TRUE(),
"Revenue" IN ALLSELECTED(Metrics[Metrics]), [Total Rev],
"Units" IN ALLSELECTED(Metrics[Metrics]), [Hearing Aids Units],
[Hearing Aids Units]
)
Static_SelectedMeasureFilteredMTDLY =
SWITCH(TRUE(),
'Covid Adjustment'[Covid Adjustment] = "NO",
CALCULATE([Static_SelectedMeasureUnfiltered], 'Sales Data_Standard'[DAX_IsMTDLY]="Y") ,
'Covid Adjustment'[Covid Adjustment] = "YES",
CALCULATE([Static_SelectedMeasureUnfiltered], 'Sales Data_Standard'[DAX_IsMTDLYCOVIDADJ]="Y")
)
The IN operator is executing CONTAINSROW in the background. The second SWITCH is functioning as a filter, so that's what's driving the error.
There are a lot of options for rewriting this. Here is one:
Static_SelectedMeasureUnfiltered =
CALCULATE(
SWITCH(TRUE(),
"Revenue" IN ALLSELECTED(Metrics[Metrics]), [Total Rev],
"Units" IN ALLSELECTED(Metrics[Metrics]), [Hearing Aids Units],
[Hearing Aids Units]
)
Static_SelectedMeasureFilteredMTDLY =
SWITCH(TRUE(),
'Covid Adjustment'[Covid Adjustment] = "NO",
CALCULATE([Static_SelectedMeasureUnfiltered], 'Sales Data_Standard'[DAX_IsMTDLY]="Y") ,
'Covid Adjustment'[Covid Adjustment] = "YES",
CALCULATE([Static_SelectedMeasureUnfiltered], 'Sales Data_Standard'[DAX_IsMTDLYCOVIDADJ]="Y")
)