How to write a command like if-else in DAX?

[Akira_07]

I'm using Dax filter from SQL Server Analysis Service to subset a column

I have a variable named `const` computed by

VAR const = if(..., 1, 0) RETURN const

The values of `const` are `0` and `1`. I have two commands. The FIRST one is to return a subset of column `SALESREP_WID` from table `Dim - SalesRep` based on some conditions of the table `Dim - Security SalesRep`, i.e.,

'Dim - SalesRep'[SALESREP_WID] IN CALCULATETABLE(VALUES('Dim - Security SalesRep'[SALESREP_WID]),
FILTER('Dim - Security SalesRep', 'Dim - Security SalesRep'[USER_NAME] = USERNAME()))

The SECOND is to return the full column `SALESREP_WID` of table `Dim - SalesRep`, i.e.,

'Dim - SalesRep'[SALESREP_WID] IN CALCULATETABLE(VALUES('Dim - SalesRep'[SALESREP_WID]))

What I want is as follow: I compute `const` each time. If the value of `const` is `1`, I would like to execute the FIRST command. If the value of `const` is `0`, I would like to execute the SECOND command. This is somehow like the if-else mechanism.

Could you elaborate on how to combine these three commands into the one that does the job?

[Akira_07]

Thank you so much for your help! It works perfectly. You solution helps me reduce the work from 80 mil rows to 8000 rows.

[FreemanZ · Accepted solution]

hi @Akira_07

like this?

Measure = 

VAR const = if(..., 1, 0)

RETURN

IF(

    const = 1,

    'Dim - SalesRep'[SALESREP_WID] IN 

        CALCULATETABLE(

            VALUES('Dim - Security SalesRep'[SALESREP_WID]),

            'Dim - Security SalesRep'[USER_NAME] = USERNAME()

        ),

    'Dim - SalesRep'[SALESREP_WID] IN 

        CALCULATETABLE(VALUES('Dim - SalesRep'[SALESREP_WID]))

)

but it return TRUE/FALSE, is it what you expect?