cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User IV
Super User IV

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

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

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

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

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

@Greg_Deckler Sorry , its a measure that i want to calculate based on selected value.

Highlighted
Super User IV
Super User IV

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

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

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

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

Highlighted
Super User IV
Super User IV

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

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Helper I
Helper I

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

That sounds interesting , let me try that 🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors