topic Re: DAX Command - IF variables, SWITCH and COALESCE in DAX Commands and Tips

DAX Command - IF variables, SWITCH and COALESCE

khisla — Tue, 29 Jul 2025 18:50:55 GMT

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

Re: DAX Command - IF variables, SWITCH and COALESCE

Cookistador — Tue, 29 Jul 2025 19:15:51 GMT

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 )

Re: DAX Command - IF variables, SWITCH and COALESCE

V-yubandi-msft — Wed, 30 Jul 2025 05:50:09 GMT

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.

Re: DAX Command - IF variables, SWITCH and COALESCE

khisla — Wed, 30 Jul 2025 08:10:14 GMT

Got an error message

Re: DAX Command - IF variables, SWITCH and COALESCE

Cookistador — Wed, 30 Jul 2025 09:08:11 GMT

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 )

Re: DAX Command - IF variables, SWITCH and COALESCE

V-yubandi-msft — Wed, 30 Jul 2025 09:24:25 GMT

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.