Slicer selection preserving but also being overwritten

[LupinAinsworth]

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! 

[LupinAinsworth]

Thanks @Shravan133 @Anonymous! 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.

[Anonymous]

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:

1. Create a calculated table as the slicer

Slicer = VALUES('Table'[City])

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.

Output:

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]

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
)