Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors