Calculate measure based on Filter selection

[SR1]

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!

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

[octa_jk]

Can not access this file. is there a solution to this problem?

[Anonymous]

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

[v-chuncz-msft · Accepted solution]

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

[Anonymous]

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]

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

[Anonymous]

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