Forum Discussion

Alex_Wynen's avatar
Alex_Wynen
New Member
3 years ago

Help to understand a complex DAX query

Hi,

I am learning Power DAX, and I saw my previous workmate wrote a DAX query, but it is too complex to understand for me, could anyone help me understand it? like which part of the query runs first e.g. the last 'if' statement or the first 'if' statement. 

Many thanks

The query is below:

 

MyFlag =

IF(

     CALCULATE(COUNTROWS(Player), Player[PlayerScopeFlagDesc] = "Out Of Scope") = CALCULATE(COUNTROWS(Player)), "Out of Scope",

          IF(

               CALCULATE(COUNTROWS(Player), Player[PlayerScopeFlagDesc] = "Out Of Scope") > 0 && BillGroup[BillGroupName] = "MasterPlayerNo", "Out of Scope",

               IF(

                    CALCULATE(COUNTROWS(Player), Player[PlayerScopeFlagDesc] = "TBD") > 0, "TBD",

                     IF(

                         CALCULATE(COUNTROWS(Player), Player[PlayerScopeFlagDesc] = "In Scope") > 0, "In Scope", "Migrated"

                        )

                  )

         )

)

1 Reply

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Icon for Solution Sage rankSolution Sage
    3 years ago

    In this expression, the first 'if' will get executed first and then the others in the same order.

     

    It would be better to use the SWITCH statement instead of IF statement.

    Here is the expression for switch statement:

    MyFlag =
    SWITCH (
    TRUE (),
    CALCULATE ( COUNTROWS ( Player ), Player[PlayerScopeFlagDesc] = "Out Of Scope" ) = CALCULATE ( COUNTROWS ( Player ) ), "Out of Scope",
    CALCULATE ( COUNTROWS ( Player ), Player[PlayerScopeFlagDesc] = "Out Of Scope" ) > 0 && BillGroup[BillGroupName] = "MasterPlayerNo", "Out of Scope",
    CALCULATE ( COUNTROWS ( Player ), Player[PlayerScopeFlagDesc] = "TBD" ) > 0, "TBD",
    CALCULATE ( COUNTROWS ( Player ), Player[PlayerScopeFlagDesc] = "In Scope" ) > 0, "In Scope",
    "Migrated"
    )