Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
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.
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.
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
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |