topic Re: Can you provide alternate dax without using SWITCH statement? in DAX Commands and Tips

Can you provide alternate dax without using SWITCH statement?

Maha_0221 — Sat, 04 May 2024 07:41:08 GMT

Suppose you have a table called Sales with columns for Region, Product, and Revenue.
You need to create a measure that categorizes the Revenue based on the Region using the following criteria:

If the Region is "North America" or "Europe", categorize the Revenue as "Developed Markets".
If the Region is "Asia" or "South America", categorize the Revenue as "Emerging Markets".
If the Region is anything else, categorize the Revenue as "Other Markets".
hope you can provide alternate result?

Ashish_Mathur

Re: Can you provide alternate dax without using SWITCH statement?

AntrikshSharma — Sat, 04 May 2024 07:40:51 GMT

You can create another table that has region and category and either merge them or use a relationship, but what is the issue in creating a calculated column with SWITCH?

Re: Can you provide alternate dax without using SWITCH statement?

Maha_0221 — Sat, 04 May 2024 07:42:12 GMT

report performance issue

Re: Can you provide alternate dax without using SWITCH statement?

AntrikshSharma — Sat, 04 May 2024 07:43:08 GMT

Create a calculated column not a measure

Re: Can you provide alternate dax without using SWITCH statement?

Maha_0221 — Sat, 04 May 2024 08:51:02 GMT

this was asked in interview so he want best answer for this question

Categorize_Revenue= switch(allselected(sales(region),{"North America","Europe"},"Developed Markets", {"Asia" or "South america"},"Emerging markets",other markets))

Re: Can you provide alternate dax without using SWITCH statement?

Ashish_Mathur — Sat, 04 May 2024 08:14:25 GMT

Hi,

If you insist on a measure, then try this one

Measure = if(hasonevalue(Data[Region]),if(min(Data[Region])="North America"||min(Data[Region])="Europe","Developed Markets",if(min(Data[Region])="Asia"||min(Data[Region])="South America","Emerging Markets","Other")),blank())

Hope this helps.

Re: Can you provide alternate dax without using SWITCH statement?

Anonymous — Tue, 07 May 2024 06:29:31 GMT

Hi Maha_0221

Ashish_Mathur AntrikshSharma Thank you very much for your prompt reply. Allow me to offer a different approach here.

Maha_0221 For your question, here is the method I provided:

Here's some dummy data

“Sales”

You can create a measure. By calling a variable and making an if judgment.

Measure = VAR _REGION = SELECTEDVALUE('Sales'[region]) RETURN IF( _REGION in {"North America", "Europe"}, "Developed Narkets", IF( _REGION in {"Asia", "South America"}, "Emerging Markets", "Other Markets"))

Using variables can better help you minimize the impact on performance.

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.