Forum Discussion

afwork's avatar
afwork
Frequent Visitor
1 year ago
Solved

Switch not working when slicer is selected

Hello,

 

Please help. I am at a loss with this one. 

This is my first time using Power BI and I have been trying to figure out why when I filter (using a slicer), the amount in Revenue does not appear but it does when there is no filter. 

 

[01Revenue] is a measure adding up [02Revenue_Apple] + [03Revenue_Orange]

I have changed [01Revenue] with a static number and that too did not work.

I have replaced MAX with SELECTEDVALUE, added true. 

 

 
 
Actual =
SWITCH(
MAX(BTCDisplay[Order]),
1,FORMAT([01Revenue],"#,##0"),
2,FORMAT([02Revenue_Apple],"#,##0"),
3,FORMAT([03Revenue_Orange],"#,##0"),
 
""
)
 
Your help is greatly appreacited.
 
Thanks
  • TomasAndersson's avatar
    TomasAndersson
    1 year ago

    Hi, thanks for the data!
    Names on tables and columns and data are bit different compared to your screenshot so it's difficult for me to verify that this is what you're looking for. But you can find the .pbix here and check. If I've misunderstood how your data is structured, please let me know.

     

    I'm assuming now there is a relationship between the tables. 

     

    I needed to add ALL('BTCDisplay table') to the total Revenue measure to make it sum in the table, like this:

    EBITDA_Revenue = 
CALCULATE(
    [EBITDA_Revenue_Apple] + [EBITDA_Revenue_Orange],
    ALL('BTCDisplay table')
)

    But with this it seems to work.

    Does this fix your problem?

     

  • afwork's avatar
    afwork
    1 year ago

    I figured out why they work now. 

    Since I removed the BTCDisplay table and then reinstated it. It removed the relationship between the tables. When I add the relationship again, the switch dax on [01Revenue] does not work. 

9 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Icon for Solution Sage rankSolution Sage
    1 year ago

    Not sure, but let's troubleshoot. 

    If you just have [01Revenue] as its own KPI card. Does it show a result with and without applied slicers?

    If it does, it's likely the SWITCH statement. If it doesn't I would think it is the model.

     

    Is there a reason that you use FORMAT(, ""#,##0") on all values and not just having the 01, 02, 03 measures formatted like that from the beginning?

    • afwork's avatar
      afwork
      Frequent Visitor
      1 year ago

      01Revenue works with applied slicers. If you can see the images I have attached, one is with applied slicer (Office = 0105) and one without.

       

      FORMAT was an after thought and it does not make a difference as I have tried SWITCH without it.

       

      Thanks so much for replying. 

      • TomasAndersson's avatar
        TomasAndersson
        Icon for Solution Sage rankSolution Sage
        1 year ago

        Then I'm thinking it's the model and evaluation context. Somehow when Office 0105 is selected, MAX(BTCDisplay[Order]) does not equal 1 for that row. 

         

        Are you able to share the model, or what the tables look like?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi afwork ,

     

    Would you consider providing the PBIX file(Please be sure to remove any sensitive data.)? It might help us better understand the issue.

     

    Best Regards,

    Bof