cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
willdiles
Frequent Visitor

Issue with visuals not diplaying in BI Service or published to Web but work fine in Desktop

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:

 

Static_SelectedMeasureFilteredMTDLY =
CALCULATE(SWITCH(
TRUE(),
"Revenue" IN ALLSELECTED(Metrics[Metrics]), [Total Rev],
"Units" IN ALLSELECTED(Metrics[Metrics]), [Hearing Aids Units],
[Hearing Aids Units]
),
SWITCH(TRUE(),
"NO" IN ALLSELECTED('Covid Adjustment'[Covid Adjustment]), 'Sales Data_Standard'[DAX_IsMTDLY]="Y" ,
"YES" IN ALLSELECTED('Covid Adjustment'[Covid Adjustment]) , 'Sales Data_Standard'[DAX_IsMTDLYCOVIDADJ]="Y"
)
)

 

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!

 

 

1 ACCEPTED SOLUTION
DataMinistry
New Member

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




View solution in original post

1 REPLY 1
DataMinistry
New Member

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




Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors