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

## 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]))`

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

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

Proud to be a Super User!

6 REPLIES 6
Highlighted
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.

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

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

Proud to be a Super User!

Highlighted
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

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

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

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

Proud to be a Super User!

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

Highlighted
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]))`

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

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

Proud to be a Super User!

Highlighted
Helper I

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

That sounds interesting , let me try that 🙂

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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