cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculate measure based on Filter selection

Hi,

I would like to create a measure that is based on filter selection.

Here's an example:

I am using two filters – ‘Area – South’ filter is for ‘SOUTH’ visual(edited filter interaction)

‘Area – North’ filter is for ‘NORTH’ visual.

Problem: I am using measures for location count & sum of male & female.

The measure doesn’t work when I select the filters (see image) .

I want the measure to calculate according to the filter selection. In the below scenario I want the measure to show LocationCount =2 , SumMale = 94 , SumFemale = 67.

I have attached the sample pbix as well . Any help would be really appreciated. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculate measure based on Filter selection

@SR1,

You may turn Single Select to Off to allow selecting multiple items.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#selection-controls-...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Member

## Re: Calculate measure based on Filter selection

It's due to South visuals interacting and filtering North visuals and vice versa. See below link to fix

https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

Regular Visitor

## Re: Calculate measure based on Filter selection

Hi @chirayuw I am having the same problem even after changing the North & South visual interaction.

Member

## Re: Calculate measure based on Filter selection

Ah ok I understand what you're tryig to do now. Simply put its not possible the way you're trying to do it.

There are 2 slicers but both refer to the same column. Thats why a count can't be created when you have 2 different selections from 2 different slicers on the same column. You'd be better off doing something like this

https://ufile.io/9xjic

Community Support Team

## Re: Calculate measure based on Filter selection

@SR1,

You may turn Single Select to Off to allow selecting multiple items.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#selection-controls-...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Calculate measure based on Filter selection

My suggestion would be to create two calculated tables from your DataTable representing North and South locations.

I named them 'North Filter' and 'South Filter'.

You can achieve this with:

North Filter = all('DataTable'[LocationId];'DataTable'[Location])

South Filter = all('DataTable'[LocationId];'DataTable'[Location])

I would than create for each calculated table, one inactive relationships with DataTable.

Calculated tables to be used as filters.

After that create the following measures:

```Location Count=
COUNTROWS (
SUMMARIZE ( UNION ( 'North Filter', 'South Filter' ), [LocationId] )
)

```

This will give you all the distinct locations selected.

You can create also:

```North Count=CALCULATE (
COUNTROWS ( 'DataTable' ),
USERELATIONSHIP ( 'DataTable'[LocationId], 'North Filter'[LocationId] )
)```

This measure will count only the locations selected on the North Filter. I'm assuming that when nothing is selected the user wants ALL.

If you setup a table visual whith a filter on this last measure for only values greater than zero, you'll get the North information.

The same thing should be done for South.

Other measures created are:

```North Female=
CALCULATE (
SUM ( 'DataTable'[Female] ),
USERELATIONSHIP ( 'DataTable'[LocationId], 'North Filter'[LocationId] )
)

North Male=
CALCULATE (
SUM ( 'DataTable'[Male] ),
USERELATIONSHIP ( 'DataTable'[LocationId], 'North Filter'[LocationId] )
)

South Female
CALCULATE (
SUM ( 'DataTable'[Female] ),
USERELATIONSHIP ( 'DataTable'[LocationId], 'South Filter'[LocationId] )
)
South Male=
CALCULATE (
SUM ( 'DataTable'[Male] ),
USERELATIONSHIP ( 'DataTable'[LocationId], 'South Filter'[LocationId] )
)```

Finaly, measures for the Totals are created:

```Total Female = [North Female] + [South Female]

Total Male = [North Male] + [South Male]```

Final Result

Here is a link to the changes necessary for my suggestion: Sample Pbix