cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pontushaglund Frequent Visitor
Frequent Visitor

Iterate through filter values

Hi all.

I'm trying to create a measure to filter a table based on two multi-select slicers that exist on the page (without relationships to the table or each other).
The case is simple: 
Slicer 1: Year: 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023
Slicer 2: Age: 10 15 20 25 30 40 50 60 70 80 90 100
The table contains persons with the columns Name and Birthyear. I want to filter the table on the measure to only show the persons that for example turn 30 or 40 during the years 2018 or 2019 (would filter on all persons with BirthYear 1978, 1979, 1988, 1989).

I'm thinking this should be an iterative measure (SUMX?) that would iterate twice - over the selected Years and over the selected Ages.

 

The measure I've tried using (that does not work):

ShowThisRow = 
SUMX(
   VALUES(Year[Year]);
   SUMX(
      IF( SELECTEDVALUE( Year[Year] ) - SELECTEDVALUE( SelectAge[Age] ) = MAX( Person[BirthYear] ); 1; 0)
   )
)

 

Ideas where I am going wrong?

 

Thanks in advance,

 

Pontus

1 ACCEPTED SOLUTION

Accepted Solutions
magsod Member
Member

Re: Iterate through filter values

Hi,
I would probably first declare a variable that is a table of all valid birth years for the combination of slicers.
Then I would see if the birth year for the currently displayed person (for example in a table visual) exists in the list of valid years.

 

Example measure could be something like this:

 

Show =
VAR Validyears =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( SelectAge[Age] )VALUES ( Years[Years] ) );
            "ValidYear"; [Years] - [Age]
        );
        "ValidYear"; [ValidYear]
    )
RETURN
    IF (
        COUNTROWS ( INTERSECT ( VALUES ( Person[Birthyear] ); Validyears ) ) > 0;
        1;
        BLANK ()
    )

 

Br,

Magnus

View solution in original post

2 REPLIES 2
magsod Member
Member

Re: Iterate through filter values

Hi,
I would probably first declare a variable that is a table of all valid birth years for the combination of slicers.
Then I would see if the birth year for the currently displayed person (for example in a table visual) exists in the list of valid years.

 

Example measure could be something like this:

 

Show =
VAR Validyears =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( SelectAge[Age] )VALUES ( Years[Years] ) );
            "ValidYear"; [Years] - [Age]
        );
        "ValidYear"; [ValidYear]
    )
RETURN
    IF (
        COUNTROWS ( INTERSECT ( VALUES ( Person[Birthyear] ); Validyears ) ) > 0;
        1;
        BLANK ()
    )

 

Br,

Magnus

View solution in original post

Highlighted
pontushaglund Frequent Visitor
Frequent Visitor

Re: Iterate through filter values

Excellent, works like a charm. Thanks a bunch!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 140 members 1,677 guests
Please welcome our newest community members: