Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Switch True

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's avatar
    Rahal
    6 years ago

    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 :

     

10 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • Rahal's avatar
    Rahal
    Frequent Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?

  • Rahal's avatar
    Rahal
    Frequent Visitor
    6 years ago

    Hi,

     

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
    Rahal
    Frequent Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
    Rahal
    Frequent Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      I appreciate all your help on this!