Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Winniethewinner
Helper III
Helper III

DAX help: switch selectedvalue not working

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.  

10 REPLIES 10
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1652230953491.png


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.

Hi @v-yalanwu-msft 

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

@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
Community Support
Community Support

Hi, @Winniethewinner ;

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

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.

 

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

Winniethewinner_0-1652194669581.png

2022-05-10_10-58-59.png

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. 

Winniethewinner_2-1652194980092.png

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. 

@Winniethewinner 

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

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

Winniethewinner_0-1652196226984.png

2022-05-10_11-24-11.png

tamerj1
Super User
Super User

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
Continued Contributor
Continued Contributor

hi @Winniethewinner ,

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

@m3tr01d they are measures.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors