topic Re: DAX help: switch selectedvalue not working in DAX Commands and Tips

DAX help: switch selectedvalue not working

Winniethewinner — Fri, 06 May 2022 20:15:37 GMT

Hi everyone, I have a switch DAX. However when I add it to the visual, it shows "Can't display the visual":

Tickets_WOW_% = SWITCH(SELECTEDVALUE(Tickets_Measures[Name]), "Revenue", IF(AND(Tickets_Measures[This Week]<>BLANK(),Tickets_Measures[Last Week]<>BLANK()), DIVIDE(Tickets_Measures[This Week],Tickets_Measures[Last Week])-1),BLANK(), "CPN Count", IF(AND(Tickets_Measures[This Week]<>BLANK(),Tickets_Measures[Last Week]<>BLANK()),DIVIDE(Tickets_Measures[This Week],Tickets_Measures[Last Week])-1,BLANK()), "Rev per CPN", IF(AND(Tickets_Measures[This Week]<>BLANK(),Tickets_Measures[Last Week]<>BLANK()), DIVIDE(Tickets_Measures[This Week],Tickets_Measures[Last Week])-1,BLANK()))

I know by adding FORMAT in front of DIVIDE would work, but that doesn't support chart.

Would someone please help me to fix it? Thanks.

Re: DAX help: switch selectedvalue not working

m3tr01d — Fri, 06 May 2022 23:26:37 GMT

hi Winniethewinner ,

Are Tickets_Measures[This Week] and Tickets_Measures[Last Week] Measures or Columns?

Re: DAX help: switch selectedvalue not working

Winniethewinner — Sat, 07 May 2022 00:51:11 GMT

m3tr01d they are measures.

Re: DAX help: switch selectedvalue not working

tamerj1 — Sat, 07 May 2022 02:59:30 GMT

Hi Winniethewinner

the location of the first BLANK() is wrong. However you don't need to specify BLANK () as the 2nd argument of IF as it is blank by default.

Tickets_WOW_% = SWITCH ( SELECTEDVALUE ( Tickets_Measures[Name] ), "Revenue", IF ( AND ( [This Week] <> BLANK (), [Last Week] <> BLANK () ), DIVIDE ( [This Week], [Last Week] ) - 1 ), "CPN Count", IF ( AND ( [This Week] <> BLANK (), [Last Week] <> BLANK () ), DIVIDE ( [This Week], [Last Week] ) - 1 ), "Rev per CPN", IF ( AND ( [This Week] <> BLANK (), [Last Week] <> BLANK () ), DIVIDE ( [This Week], [Last Week] ) - 1 ) )

Re: DAX help: switch selectedvalue not working

v-yalanwu-msft — Tue, 10 May 2022 02:02:30 GMT

Hi, Winniethewinner ;

As Tamerj1 said, your first if() symbol is incorrect; You can modify it.

Secondly, all your if formulas are the same. Maybe you can try to simplify it.

Tickets_WOW_% = IF ( AND ( Tickets_Measures[This Week] <> BLANK (), Tickets_Measures[Last Week] <> BLANK () ), DIVIDE ( Tickets_Measures[This Week], Tickets_Measures[Last Week] ) - 1 )

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX help: switch selectedvalue not working

Winniethewinner — Tue, 10 May 2022 15:07:47 GMT

Thanks for the reply. I've changed the formula as you and tamerj1 suggested, however the WOW_% shows the $ instead %.

A bit more background, I used calculation group to switch the measures and its respective number format, where Rev shows $ format and count shows as decimal number format.

If I add FORMAT to Tickets_WOW_% formula and create a chart showing WOW% by dimension, the chart will not display. Any idea how to fix it? Thanks.

Re: DAX help: switch selectedvalue not working

tamerj1 — Tue, 10 May 2022 15:14:57 GMT

Winniethewinner

Adding FORMAT will change it into a string. You need to chsnge the format manually to %

Re: DAX help: switch selectedvalue not working

Winniethewinner — Tue, 10 May 2022 15:25:29 GMT

It doesn't work ☹️, and still shows as $.

Re: DAX help: switch selectedvalue not working

v-yalanwu-msft — Wed, 11 May 2022 01:17:02 GMT

Hi, Winniethewinner ;

Are these two the same measure? It doesn't look quite the same unless you change the name.
Note that you need to change the format of the field itself.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX help: switch selectedvalue not working

tamerj1 — Wed, 11 May 2022 03:15:32 GMT

Hi v-yalanwu-msft

actually Winniethewinner is using a calculation group to force $ format ove measures

Re: DAX help: switch selectedvalue not working

Winniethewinner — Wed, 11 May 2022 13:32:06 GMT

v-yalanwu-msft Those two are the same measures - I did a rename.

tamerj1 yes I use calculation group to force the number format when switching the measures.

Here is the formula for This Week:

This Week = CALCULATE([Selected_Measure],FILTER(Tickets,Tickets[ISSUE_WEEK]=MAX(Tickets[ISSUE_WEEK])))

Here is the formula for Selected_Measure:

Selected_Measure = SWITCH(SELECTEDVALUE(Tickets_Measures[Name]), "Revenue", (SUM(Tickets[Sum(tot_rev)])), "CPN Count", (SUM(Tickets[TOT_CPN_COUNT])), "Rev per CPN", (DIVIDE(SUM(Tickets[Sum(tot_rev)]),SUM(Tickets[TOT_CPN_COUNT]))) )

The formats are correct except for this WoW % (unless I use FORMAT to make it a string - but this doesn't work in a chart, can only work in a table or matrix). I'd like to see if there is a way to make this WoW % correct format and also works in a chart.