DAX help: switch selectedvalue not working

[Winniethewinner]

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.  

[v-yalanwu-msft]

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.

[tamerj1]

Hi @v-yalanwu-msft 

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

[Winniethewinner]

@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.  

[v-yalanwu-msft]

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.

[Winniethewinner]

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. 

[tamerj1]

@Winniethewinner 

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

[Winniethewinner]

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

[tamerj1]

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
        )
)

[m3tr01d]

hi @Winniethewinner ,

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

[Winniethewinner]

@m3tr01d they are measures.