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

sample.png

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

https://drive.google.com/open?id=1iEgsHFQkFx9x_xtMHpYp6E_KWISGejaM

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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
chirayuw Member
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

SR1 Regular Visitor
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.

chirayuw Member
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
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.
Gravanita Regular Visitor
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.JPGCalculated 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.

 

North Visual.JPGNorthVisualFilter.JPG

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]

finalResult.JPGFinal Result

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