Forum Discussion
calculate a measure using a double column filter
hi, I have a data set like this : activities main area country activity 1 X A activity 1 X B activity 1 Y A activity 1 Y B activity 2 W A activity 2 W B act...
Anonymous
First create a table based on the one you showed with just the areas to serve as a filter.
Main Areas = values(Table1[main area])
Don't connect it to your existing table.
Create the following measure:
Match = VAR CountryAreas = CALCULATETABLE ( VALUES ( Table1[main area] ); ALLEXCEPT ( Table1; Table1[country] ) ) VAR filterAreas = VALUES ( 'Main Areas'[main area] ) VAR MatchingAreas = INTERSECT ( CountryAreas; filterAreas ) RETURN IF ( COUNTROWS ( filterAreas ) = COUNTROWS ( MatchingAreas ); "Match"; "No Match" )You should get this result
Slicer are values from the disconnected table
Anonymous
First create a table based on the one you showed with just the areas to serve as a filter.
Main Areas = values(Table1[main area])
Don't connect it to your existing table.
Create the following measure:
Match =
VAR CountryAreas =
CALCULATETABLE (
VALUES ( Table1[main area] );
ALLEXCEPT ( Table1; Table1[country] )
)
VAR filterAreas =
VALUES ( 'Main Areas'[main area] )
VAR MatchingAreas =
INTERSECT ( CountryAreas; filterAreas )
RETURN
IF (
COUNTROWS ( filterAreas ) = COUNTROWS ( MatchingAreas );
"Match";
"No Match"
)You should get this result
Slicer are values from the disconnected table
Genius!
thank you! it works.
Just another question: there is a way to show only the activities that match with the main area selection? ...because in this case the table is showing all the activities in the countries that match....
thank you again..