DAX Command - IF variables, SWITCH and COALESCE

[khisla]

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 Store	Exist Store	Default Store	Result Store	Comments
IL213		IL213	IL213
	IL213	IL213	IL213
		IL213	IL213
IL213		IL135	IL213
	IL213	IL135	IL213
IL135	IL135	IL135	IL135
IL213	IL213	IL135	IL213

[V-yubandi-msft]

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.

[khisla]

Got an error message

[V-yubandi-msft]

Hi @khisla ,

Have you been able to review the PBIX file I attached? If not, please check it when you have a moment. The logic in that file has been implemented correctly.

Thank You.

[Cookistador · Accepted solution]

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
    )

[Cookistador]

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
    )