Reply
Frequent Visitor
Posts: 5
Registered: ‎12-02-2018
Accepted Solution

calculate a measure using a double column filter

hi, I have a data set like this :

 

activities main areacountry
activity 1XA
activity 1XB
activity 1YA
activity 1YB
activity 2WA
activity 2WB
activity 2WC
activity 2WD
activity 2YA
activity 2YB
activity 2YC
activity 2YD
activity 3ZE
activity 3ZF
activity 3WE
activity 3WF
activity 3XE
activity 3XF
activity 3YE
activity 3YF
activity 3VE
activity 3VF

 

and i have to solve this question: which countries have proyects with X,Y,and Z main areas (AND ,not or, thats the reason why a conventional slicer cant help me) ...so i know that i have to filter first by country and in this filtered escenario i must evaluate if there are at least three rows with the three main areas im looking for...but i dont know how..can anyone help me? PLEASEEEE  


Accepted Solutions
Regular Visitor
Posts: 39
Registered: ‎07-09-2017

Re: calculate a measure using a double column filter

@danielvarela92

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.

 

 

Capture.JPG

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 

Capture.JPGSlicer are values from the disconnected table

 

 

 

View solution in original post


All Replies
Regular Visitor
Posts: 39
Registered: ‎07-09-2017

Re: calculate a measure using a double column filter

@danielvarela92

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.

 

 

Capture.JPG

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 

Capture.JPGSlicer are values from the disconnected table

 

 

 

Frequent Visitor
Posts: 5
Registered: ‎12-02-2018

Re: calculate a measure using a double column filter

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

Highlighted
Regular Visitor
Posts: 39
Registered: ‎07-09-2017

Re: calculate a measure using a double column filter

If I understood your question you can do this:

 

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 );
        IF (
            ISEMPTY ( INTERSECT ( VALUES ( Table1[main area] ); filterAreas ) );
            "No Match";
            "Match"
        );
        "No Match"
    )

Here, a new condition was added to check if the current record has a filtered area.