The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
hello
How can i pass multiple selected value in a slicer as a filter to DAX formula ?
Solved! Go to Solution.
If you have data such that Location and Sublocation are slicers, so your data is perhaps like this:
Location,Sublocation,Data
Location1,Location1-1,data1
Location1,Location1-2,data2
Location1,Location1-3,data3
Location2,Location2-1,data4
Location2,Location2-2,data5
Location3,Location3-1,data6
You have slicer for Location and a slicer for Sublocation
User selects Location 1 from Location slicer and then Location 1-1 and Location1-3 from Sublocation slicer, then you would want measures like this:
Measure1 = CALCULATE(SUM([Data]),ALLEXCEPT([Location])) Measure2 = SUM([Data])
Measure 2 preservers all of the context that the user has chosen.
Measure 1 ignores the concext of everything except Location.
You could also get fancy and do:
Measure 1 = CALCULATE([Measure2],ALLEXCEPT([Location]))
It will do that automatically as a part of the way DAX works in context.
@Greg_Deckler Sorry , its a measure that i want to calculate based on selected value.
Right, let's say that you have data like the following:
Customer, Sales
Customer 1, 200
Customer 1, 300
Customer 2, 300
Customer 3, 100
If you create a measure like:
Measure = SUM([Sales])
You put that measure in a visual and then have a slicer for Customer then when you select Customer 1 and Customer 3 from the slicer you will get a value of 600 for your measure.
If that is not what you are after, then it is unclear what you are going for.
@Greg_Deckler I should have asked liked this in first place ,i hope it conveys my question well .
I have a location table with
Location 1 |
Location 2 |
Location 3 |
And a Sublocation
Sub Location 1-1 |
Sub location 1-2 |
Sub location 2-1 |
sub location 2-2 |
sub location 3-1 |
I am putting Location and sublocation as Slicer visual so user can pick and select .
My data Table is like this
Location 1 | sub Location 1-1 | data1 |
location1 | sub location1 -10 | data2 |
location1 | sub location 1-2 | data3 |
Now i need a Report which shows
Location1 | AllData in Location 1 i.e (Data1+data2+data3) | Data in only Sub location1-1 and sub location 1-2 i.e.(data1+data3) |
and so for all locations , how should i achieve it ?
If you have data such that Location and Sublocation are slicers, so your data is perhaps like this:
Location,Sublocation,Data
Location1,Location1-1,data1
Location1,Location1-2,data2
Location1,Location1-3,data3
Location2,Location2-1,data4
Location2,Location2-2,data5
Location3,Location3-1,data6
You have slicer for Location and a slicer for Sublocation
User selects Location 1 from Location slicer and then Location 1-1 and Location1-3 from Sublocation slicer, then you would want measures like this:
Measure1 = CALCULATE(SUM([Data]),ALLEXCEPT([Location])) Measure2 = SUM([Data])
Measure 2 preservers all of the context that the user has chosen.
Measure 1 ignores the concext of everything except Location.
You could also get fancy and do:
Measure 1 = CALCULATE([Measure2],ALLEXCEPT([Location]))
That sounds interesting , let me try that 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
107 | |
76 | |
61 |
User | Count |
---|---|
276 | |
129 | |
122 | |
100 | |
88 |