Iterate through filter values

[pontushaglund]

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

[magsod · Accepted solution]

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

[pontushaglund]

Excellent, works like a charm. Thanks a bunch!