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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
LupinAinsworth
Frequent Visitor

Slicer selection preserving but also being overwritten

Hello, 

I am trying to build a dax measure that would allow me to respect the selection in a slicer but then overwrite it to calculate differently. So, let's say I have city in slicer but one of my visuals in my report I do not want to see the specific city, instead, I want to see the corresponding state. Neither the city or state are included in the visual.

How does one do this? I feel like it uses calculate but I can't put the pieces together. It's almost like a substitute or lookup or related but the city and state are on the same table. Thank you so much for your help! 

3 REPLIES 3
LupinAinsworth
Frequent Visitor

Thanks @Shravan133 @v-xuxinyi-msft! I tried your suggestions but the output isn't quite what I am looking for. Please see sample data below. If I put city in slicer I want the measure to return the state value, so for example, if I select LA in the slicer I want to see a value of 20.

 

LupinAinsworth_0-1721747673922.png

 

v-xuxinyi-msft
Community Support
Community Support

Hi @LupinAinsworth 

 

Thanks for the reply from @Shravan133 , please allow me to provide another insight:

 

@LupinAinsworth , according to your description, I created a simple sample data, I hope it can help you.

 

Sample data:

vxuxinyimsft_0-1721703346949.png

 

1. Create a calculated table as the slicer

Slicer = VALUES('Table'[City])

vxuxinyimsft_1-1721703400226.png

 

2. Create a mesure as follow

Measure = IF(SELECTEDVALUE(Slicer[City]) =  BLANK(), 1, IF(MAX([City]) = SELECTEDVALUE(Slicer[City]), 1, 0))

 

3. Put the measure into the visual-level filters, set up show items when the value is 1.

vxuxinyimsft_2-1721703464383.png

 

Output:

vxuxinyimsft_3-1721703556763.png

 

If this is not the result you want, please provide some sample data and the expected result based on the sample data. Then we can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Shravan133
Super User
Super User

Hi,

 

Try this: 

Sales by State =
CALCULATE(
SUM(LocationTable[Sales]),
REMOVEFILTERS(LocationTable[City]),
VALUES(LocationTable[State])
)

 

or use ALLSELECTED function

 

Sales by State =
VAR SelectedCity = SELECTEDVALUE(LocationTable[City])
VAR StateOfSelectedCity =
CALCULATE(
VALUES(LocationTable[State]),
LocationTable[City] = SelectedCity
)
RETURN
CALCULATE(
SUM(LocationTable[Sales]),
ALLSELECTED(LocationTable[State]) -- This will respect the selected state while ignoring the city filter
)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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