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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
afwork
Frequent Visitor

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. 

 

afwork_1-1728978774782.pngafwork_2-1728978791492.png

 
 
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
2 ACCEPTED SOLUTIONS

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. 

TomasAndersson_0-1729238522947.png

 

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.

TomasAndersson_1-1729238590792.png

Does this fix your problem?

 

View solution in original post

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. 

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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

TomasAndersson
Solution Sage
Solution Sage

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?

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. 

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?

Note: I do not necessarily need to use this dax. I just want to be able to add up in the same column without using a matrix. Other suggestions are welcome. Thanks!

 

 

Here's a sample of the data.

 

BTCDisplay table

OrderMain AccountBTC_ID
1Revenue  
2VenueEBITDA_Revenue_Apple
3ProductionEBITDA_Revenue_Orange

 

PL_Transactions

BTC_IDOfficeCodeAccountingAmt   
EBITDA_Revenue_Orange0101-100000   
EBITDA_Revenue_Apple0101-100000   
EBITDA_Revenue_Orange0101-100000   
EBITDA_Revenue_Apple0101-100000   
EBITDA_Revenue_Apple0105-100000   
EBITDA_Revenue_Apple0101-100000   
EBITDA_Revenue_Orange0101-100000   
EBITDA_Revenue_Orange0105-100000   

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. 

TomasAndersson_0-1729238522947.png

 

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.

TomasAndersson_1-1729238590792.png

Does this fix your problem?

 

That worked! Thank you!!!!! Not sure if because of ALL().

I think it's because I opened your file. It mentioned something about your file being in a newer version. The reason I think that is because I have other measures adding up other measures like [01Revenue] but I didn't add ALL() to those, yet they somehow work now too. 

 

This is what I did:

1. Opened your file

2. Opened my file, added ALL to [01Revenue]. It didn't seemed to work.

3. I removed BTCDisplay in my file and added it again.

4. Slicers now work. 

 

WOW!

 

What version do you have?

 

Thank you again. I'm so excited! I really appreciated it.

 

 

Huh, strange. Can't tell you why, but great that it works now! 

 

I have version  2.137.751.0 64-bit (October 2024)

 

Glad I could help!

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. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.