Forum Discussion

Winniethewinner's avatar
Winniethewinner
Helper IV
4 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    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's avatar
      Winniethewinner
      Helper IV
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

        Winniethewinner 

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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    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.

      • Winniethewinner's avatar
        Winniethewinner
        Helper IV
        4 years ago

        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.