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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SAG1
Helper I
Helper I

multiple selected value in a slicer as a filter to DAX formula

hello

 

How can i pass multiple selected value in a slicer as a filter to DAX formula ? 

1 ACCEPTED 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]))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

It will do that automatically as a part of the way DAX works in context.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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 1sub Location 1-1data1
location1sub location1 -10data2
location1sub location 1-2data3

 

Now i need a Report which shows

 

Location1AllData 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]))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

That sounds interesting , let me try that 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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