Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Excellent, works like a charm. Thanks a bunch!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |