Forum Discussion

khisla's avatar
khisla
Helper II
1 year ago
Solved

DAX Command - IF variables, SWITCH and COALESCE

I need a DAX commend to identify the store to which an employee is based to sales commission calculation purposes

Please find table of specific scenarios and expected results for each one.

So I have parts of the DAX command however need some help combining.

 

IF(
OR(
Entrance Store = "Default Store",
'Exit Store = "Defailt Store")
),
"Default Store"

 


VAR Entrance = [EntranceStore]
VAR Exit = [ExitStore]
VAR Default = [DefaultStore]
RETURN
SWITCH(
TRUE(),
ISBLANK(Entrance) || ISBLANK(Exit), Default,
NOT ISBLANK(Entrance) && Entrance <> Default, Entrance,
NOT ISBLANK(Exit) && Exit <> Default, Exit,
Default
)

 

 

 

Entrance StoreExist StoreDefault StoreResult StoreComments
IL213 IL213IL213 
 IL213IL213IL213 
  IL213IL213 
IL213 IL135IL213 
 IL213IL135IL213 
IL135IL135IL135IL135 
IL213IL213IL135IL213 

 

DAX help needed
  • Cookistador's avatar
    Cookistador
    1 year ago

    Hello khisla 
    You added one ) in your second condition in the CASE
    Normally, the following measure should work

    Next=

 VAR EntranceStore = SELECTEDVALUE('HILAN'[Entrance Store])
 VAR ExitStore = SELECTEDVALUE('HILAN'[Exit Store])
 VAR DefaultStore = SELECTEDVALUE('HILAN'[Employee Default Store])

RETURN
   SWITCH(
   TRUE(),
   NOT(ISBLANK(EntranceStore)) && EntranceStore <> DefaultStore, EntranceStore,
   NOT(ISBLANK(ExitStore)) && ExitStore <> DefaultStore, ExitStore,
   NOT(ISBLANK(EntranceStore)) || EntranceStore = DefaultStore || ISBLANK(ExitStore) || ExitStore = DefaultStore, DefaultStore,
     DefaultStore
    )



5 Replies

  • Cookistador's avatar
    Cookistador
    Super User
    1 year ago

    Hi khisla 

    Normally, the following measure should return what you need

     

    Store Result= 
VAR EntranceStore = SELECTEDVALUE('Table'[Entrance Store])
VAR ExitStore = SELECTEDVALUE('Table'[Exit Store])
VAR DefaultStore = SELECTEDVALUE('Table'[Default Store]) 

RETURN
    SWITCH(
        TRUE(),
        EntranceStore<>"" && EntranceStore <> DefaultStore, EntranceStore,
        ExitStore<>"" && ExitStore <> DefaultStore, ExitStore,
        EntranceStore<>"" || EntranceStore = DefaultStore || ExitStore="" || ExitStore = DefaultStore, DefaultStore,
        DefaultStore
    )

     

     

    If it is not working for you (probably due to the copy/paste I did), you have to replace <> "" by not(isblank()

    So the formula would come

    Next = 
VAR EntranceStore = SELECTEDVALUE('Table'[Entrance Store])
VAR ExitStore = SELECTEDVALUE('Table'[Exit Store])
VAR DefaultStore = SELECTEDVALUE('Table'[Default Store]) 

RETURN
    SWITCH(
        TRUE(),
        NOT(ISBLANK(EntranceStore)) && EntranceStore <> DefaultStore, EntranceStore,
        NOT(ISBLANK(ExitStore))) && ExitStore <> DefaultStore, ExitStore,
        NOT(ISBlank(EntranceStore)) || EntranceStore = DefaultStore || ISBLANK(ExitStore) || ExitStore = DefaultStore, DefaultStore,
        DefaultStore
    )
  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    Hi khisla ,

    Thanks for posting your query in the Microsoft Fabric Community. I’ve validated Cookistador , approach in my Power BI report, and it works as expected in all scenarios. for your refere attached the .pbix file for you to review or verify the implementation if needed. Cookistador  response provides the expected results please review it for confirmation.

     

    Thanks, Cookistador , for sharing the correct DAX logic .

     

    Best regards,
    Yugandhar.

      • Cookistador's avatar
        Cookistador
        Super User
        1 year ago

        Hello khisla 
        You added one ) in your second condition in the CASE
        Normally, the following measure should work

        Next=

 VAR EntranceStore = SELECTEDVALUE('HILAN'[Entrance Store])
 VAR ExitStore = SELECTEDVALUE('HILAN'[Exit Store])
 VAR DefaultStore = SELECTEDVALUE('HILAN'[Employee Default Store])

RETURN
   SWITCH(
   TRUE(),
   NOT(ISBLANK(EntranceStore)) && EntranceStore <> DefaultStore, EntranceStore,
   NOT(ISBLANK(ExitStore)) && ExitStore <> DefaultStore, ExitStore,
   NOT(ISBLANK(EntranceStore)) || EntranceStore = DefaultStore || ISBLANK(ExitStore) || ExitStore = DefaultStore, DefaultStore,
     DefaultStore
    )