cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danielvarela92
Regular Visitor

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  

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

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

3 REPLIES 3
Highlighted
Anonymous
Not applicable

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

danielvarela92
Regular Visitor

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors