topic Re: DAX Switch True in DAX Commands and Tips

DAX Switch True

Anonymous — Tue, 08 Oct 2019 21:33:12 GMT

DAX experts,

Here is how my data is set up:

ID: Reason 1 Reason 2 Reason 3 Reason 4

1 Correct Incorrect Incorrect Correct

2 Incorrect Correct Correct Incorrect

3 Correct Incorrect Correct Correct

What I am trying to do is get a count of how many times each reason contains incorrect.

So Reason1 = 1; Reason2 = 2; Reason3 = 1

Right now I am doing a Switch True:

TEST = SWITCH(TRUE(),
    'Check'[Reason1] = "INCORRECT","Reason1",
    'Check'[Reason2] = "INCORRECT","Reason2",
    'Check'[Reason3] = "INCORRECT","Reason3",

This is wrong because it is saying only when reason 1 is incorrect then count the number of times reason 2 is incorrect. I basically need this to be calculated independent of what reason 1 was marked.

Re: DAX Switch True

kentyler — Tue, 08 Oct 2019 22:15:13 GMT

try changing your data to this format:

ID: Reason Response

1 1 Incorrect

1 2 Correct

1 3 Incorrect

1 4 Incorrect

2 1 Correct

2 2 Incorrect

2 3 Incorrect

2 4 Correct

it will make your calculation much easier

Re: DAX Switch True

Rahal — Wed, 09 Oct 2019 08:07:26 GMT

Hi, here's what u can do :

Test =

VAR R1=CALCULATE(count(Check[Reason 1]);Check[Reason 1]="Incorrect")

VAR R2=CALCULATE(count(Check[Reason 2]);Check[Reason 2]="Incorrect")

VAR R3=CALCULATE(count(Check[Reason 3]);Check[Reason 3]="Incorrect")

VAR R4=CALCULATE(count(Check[Reason 4]);Check[Reason 4]="Incorrect")

RETURN

"Reason1 = " & R1 & " ; Reason2 = " & R2 & " ; Reason3 = " & R3 & " ; Reason4 = " & R4

Re: DAX Switch True

Anonymous — Wed, 09 Oct 2019 12:30:19 GMT

Is that considered a new table, column or measure?

Additionally the ; that you have on lines 3-6 I cant not get to work. Should that be a comma?

Re: DAX Switch True

Anonymous — Wed, 09 Oct 2019 12:41:54 GMT

That's a good idea. The only thing is I really have around 20 reasons and an ID can appear multiple times if it was incorrect for any reason the previous time it was submitted.

So this table would then grow exponentially and be extremely large if I am understanding it correctly.

Re: DAX Switch True

Rahal — Wed, 09 Oct 2019 13:19:40 GMT

Hi,

thats a mesure, and yeah the ; on your case is a comma ,

Re: DAX Switch True

Anonymous — Wed, 09 Oct 2019 13:36:16 GMT

Rahal

That is working now thank you for all your help!

The only thing I am struggling with now is the Return statement

How do I get the return statement to use the reason as a column header and the R1 value as the value for that reason.

Re: DAX Switch True

Rahal — Wed, 09 Oct 2019 14:15:09 GMT

Hi, if i understand what you want to do, you will need to create 4 measure for each reason and name them what you want :

Ofc replace ; with ,

Reason (1) = 
VAR R1=CALCULATE(count(Check[Reason 1]);Check[Reason 1]="Incorrect")
RETURN
if(ISBLANK(R1)=TRUE();0;R1)

Re: DAX Switch True

Anonymous — Wed, 09 Oct 2019 14:33:15 GMT

That is super helpful thank you!

When I go to make it into a clustered bar chart the axis is blank. How would I create a field to put in the axis so it labels all the bars as what the reason is?

Re: DAX Switch True

Rahal — Wed, 09 Oct 2019 15:50:55 GMT

So, with the structure of your data you can only get something like this :

Otherwise you need to transform your data, for that u need to use SQL,

here i'm ganna start by creating my table Check :

Then i can tranform it also with Sql :

So finaly i can get what i need on Power BI :

Re: DAX Switch True

Anonymous — Wed, 09 Oct 2019 15:55:26 GMT

I appreciate all your help on this!