Switch Measure Not Showing Grand Total

DAX DAX Calculations
1

Hi,

I seem to be having an issue with a switch statement, where the measure will will not give me a grand total.

My DAX measures and a screenshot is attached.

Thanks in advance

Adviser Has Target = SELECTEDVALUE('Access Adviser Appointments Table'[AdviserTarget])

All Adviser Actual Capacity = CALCULATE(SUM('Access Adviser Appointments Table'[ActualTarget]),
                                FILTER('Access Adviser Appointments Table',
                                    'Access Adviser Appointments Table'[AdviserTarget]="Yes"))

All Adviser Seen Appts = [All Adviser Total Appts]-[All Adviser Cancelled Appts]

All Adviser Capacity = SWITCH(TRUE(),
                        [Adviser Has Target]="Yes",[All Adviser Actual Capacity],
                            [Adviser Has Target]="No", [All Adviser Seen Appts]
                    )

image

2

The reason it’s not showing anything is because you have placed no value in the formula for when there is no ‘yes’ or ‘no’ result (ie. the total). You have left it blank right at the end.

To fix this quickly I would create another formula using this pattern and it should solve the total issue for you

Iteration Demo 1 = 
SUMX( 
   SUMMARIZE( 'Access Adviser Appointments Table', 'Access Adviser Appointments Table'[Advisor],
       "Adviser Capacity", [All Adviser Capacity] ),
            [Adviser Capacity] )
3

Hi Sam,

Thank you for this. Exactly what I was after.

I left the switch statement intentionally blank at the end as i was not getting the expected result previously.

5

Hi @LisaKBI1129. Take a look at this DAX pattern and see if that helps.
Fix Incorrect Totals
Greg

7

it would be helpful to see your measure, and possibly even to see a PBIX that recreates your problem.

Also, in the future, please note that your question will get better attention on the forum if you post in a new topic (See the “Make Sure Your Question has Everything Required to be Answered Effectively” link at the top of the forum for more info on that)

8

My measure is included in this message above. But I’ve attached the file. Thanks for your help.
Testing for EntDNA.pbix (2.1 MB)

9

so, your issue is exactly the same as the original poster, you need to define something to appear when the result of the SWITCH is not true.

Let’s breakdown your measure:

IF( ISCROSSFILTERED(‘PP and VES METRIC SELECTION STATUS’[METRIC])

this portion is looking to see if the METRIC column is part of the filter. Or from the Microsoft docs:

Return value TRUE when columnName or another column in the same or related table is being filtered. Otherwise returns FALSE .

if that’s true, it moves to the next part of the measure:

SWITCH(TRUE(),
[PP and VES STATUS METRIC Selected] = “PP MIR Pending”, [Testing PP MIR Review Count for Pending status],
BLANK()

it’s important to note that, using SWTICH/TRUE is another True/False test. By adding TRUE to the SWITCH measure, you are telling the report to look until it finds a TRUE response. So in the first row of your visual - the measure finds that the selected metric is PP MIR Pending, that is true, so it returns the 'Testing PP MIR Review Count" as you instruct it.

But when the measure gets to your Total row, it fails because you haven’t told it what ‘else’ to do

the SWITCH measure looks for:

  • Expression to be evaluated - in your measure this is TRUE()
  • Value a constant value to be matched with the results of the expression - PP and VES STATUS METRIC Selected] = “PP MIR Pending”
  • Result to return if Value matches Expression - [Testing PP MIR Review Count for Pending status]
  • … NOTE that Value/Result can be repeated multiple times
  • Else the value to return if nothing matches - in your measure this is BLANK()

In your measure, the total row is actually failing from the original IF statement - again, you haven’t provided anything for what the measure should do if the METRIC column isn’t filtered - and on the TOTAL row, you aren’t filtering that column.

So, what result do you want? Also, are you planning to add additional metrics to this measure (like the PP MIR Rejected, or PP MIR Valid results?)

1 Like
10

Thank you for the explanation. How should I rewrite my measure? Looking for that written example, in a time crunch pinch.

11

what result do you want? are you adding additional measures into the SWITCH? (and if so, which ones?)
I ask because that will affect how we get the a total included into the measure.

13

For a quick fix - you basically just need to add all your measures together. I have put this into a VAR (variable) at the top of the measure for an easier read of the SWITCH measure.

TESTING PP & VES Reviewed Count Selection =
VAR TotalResult = [PP MIR Review Count for Pending status] + [PP MIR Review Count for Rejected Status] + [PP MIR Review Count for Valid status] + [VES MIR Review count VES Rejected status] + [VES MIR Review count for Awaiting Review status] + [VES MIR Review count Sent to VES for Proc status]

RETURN

SWITCH(TRUE(),
[PP and VES STATUS METRIC Selected] = “PP MIR Pending”, [PP MIR Review Count for Pending status],
[PP and VES STATUS METRIC Selected] = “PP MIR Rejected”, [PP MIR Review Count for Rejected Status],
[PP and VES STATUS METRIC Selected] = “PP MIR Vaild”, [PP MIR Review Count for Valid status],
[PP and VES STATUS METRIC Selected] = “VES MIR Rejected”, [VES MIR Review count VES Rejected status],
[PP and VES STATUS METRIC Selected] = “VES MIR Awaiting Review”, [VES MIR Review count for Awaiting Review status],
[PP and VES STATUS METRIC Selected] = “VES MIR Sent to VES for Processing”, [VES MIR Review count Sent to VES for Proc status],
TotalResult )

I’m sure there is a more elegant response available, but it will take me time to review your model to better understand what each of the other measures is doing.
Note that this solution removes the ISCROSSFILTERED portion, since that did not seem to be necessary based on a quick review of your measure and report.

14

That worked!!! Thank you so much!!! My membership was totally worth it for answering my problem!!! Thanks again!!