DAX Switch True

[Anonymous]

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.

[Rahal · Accepted solution]

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 :

[Anonymous]

I appreciate all your help on this!

[Rahal]

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)

[Anonymous]

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?

[Rahal]

Hi,

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

[Anonymous]

@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.

[Rahal]

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

[Anonymous]

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?

[kentyler]

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

[Anonymous]

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.