Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
As tested with your example, when no slicers selected, the matrix and card visual all show error message.
But you only want the card visual to show error message, right?
Best Regards
Maggie
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
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.
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
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?
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |