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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eugeneniemand
Frequent Visitor

Custom Error raised on table/matrix and not on other visuals

I have the following SWITCH statement to use a different measure based on the date grain

Active Customers = SWITCH( 
TRUE(),
ISFILTERED('Calendar'[FinWeek]) && HASONEVALUE('Calendar'[FinWeek]), SUM('Fact'[WAC]),
ISFILTERED('Calendar'[FinMonth]) && HASONEVALUE('Calendar'[FinMonth]), MAX('Fact'[MAC]),
ISFILTERED('Calendar'[FinYear]) && HASONEVALUE('Calendar'[FinYear]), MAX('Fact'[YAC]),
ERROR("Please use a Date selection and only use a single selection")
)

This works fine for a card and bar charts, however when adding the same measure and dimension to a table it is raising the error. If I remove the error message, the matrix actually produces the correct result.

 

Any ideas why this may be happening?

 

8 REPLIES 8
v-juanli-msft
Community Support
Community Support

Hi @eugeneniemand 

As tested with your example, when no slicers selected, the matrix and card visual all show error message.

Capture3.JPG

But you only want the card visual to show error message, right?

 

Best Regards

Maggie

amitchandak
Super User
Super User

Use isinscope, to handle if they are available for filter

https://docs.microsoft.com/en-us/dax/isinscope-function-dax

 

Or handle overall error using https://docs.microsoft.com/en-us/dax/iferror-function-dax

But I think first condition should be all these three are not filter or not in the scope

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

 

Thanks @amitchandak  I have tried changing it to 

ISINSCOPE('Calendar'[FinWeek]) || HASONEVALUE('Calendar'[FinWeek]), SUM('Fact'[WAC]), 
ISINSCOPE('Calendar'[FinMonth])|| HASONEVALUE('Calendar'[FinMonth]), MAX('Fact'[MAC]),
ISINSCOPE('Calendar'[FinYear]) || HASONEVALUE('Calendar'[FinYear]), MAX('Fact'[YAC]),
ERROR("...")

But it still raises an error when I enable the subtotals. I want the error for if they add it to something like a card. Please see attached file https://1drv.ms/u/s!At193-CfRycChUijUaNtO1_qf2ye?e=wFHflQ 

I think each like should be and

ISINSCOPE('Calendar'[FinWeek]) && HASONEVALUE('Calendar'[FinWeek]), SUM('Fact'[WAC]), 
ISINSCOPE('Calendar'[FinMonth])&& HASONEVALUE('Calendar'[FinMonth]), MAX('Fact'[MAC]),
ISINSCOPE('Calendar'[FinYear]) && HASONEVALUE('Calendar'[FinYear]), MAX('Fact'[YAC]),
ERROR("...")

 

This still causes an error on a card and subtotals. In my previous reply I attached an example

 

Hi @eugeneniemand 

If my understanding is correct, would you like the result below?

If so, please create another measure and add this measure in the matrix visual.

Capture4.JPGCapture5.JPG

Active Customers2 =
IF (
    ISINSCOPE ( 'Calendar'[FinWeek] ),
    SUM ( 'Fact'[WAC] ),
    IF (
        ISINSCOPE ( 'Calendar'[FinMonth] ),
        MAX ( 'Fact'[MAC] ),
        IF (
            ISINSCOPE ( 'Calendar'[FinYear] ),
            MAX ( 'Fact'[YAC] ),
            SUMX (
                ALLSELECTED ( 'Calendar'[FinYear] ),
                AVERAGE ( 'Fact'[YAC] )
            )
        )
    )
)

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

This looks perfect. Thanks so much

eugeneniemand
Frequent Visitor

I have now figured out what is going on, the subtotal on the matrix obviously will not be met by any of the criteria in the switch and raises the error, which is then raised for the whole matrix and breaks the visual. I have to keep the error message to inform consumers that the measure has  to be used with a date slicer, however if they add it to a matrix I would like to ignore the error. Is there a way around this or a way to return a message like "N/A" etc?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.