topic Help to understand a complex DAX query in DAX Commands and Tips

Help to understand a complex DAX query

Alex_Wynen — Mon, 11 Sep 2023 03:45:27 GMT

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"

)

Re: Help to understand a complex DAX query

ChiragGarg2512 — Mon, 11 Sep 2023 06:57:49 GMT

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"
)