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.
With data that looks similar to this ...
Username --- Group
John Smith -- GroupA
John Smith -- GroupC
Jane Doe -- GroupB
Jane Doe -- GroupC
Mike Brown -- GroupC
Mike Brown -- GroupD
I'm trying to filter out all rows where the user is a member of GroupA or GroupB. So on the example above I would expect rows 1-4 - that is both entries for John Smith and Jane Doe to be filtered out (even the ones for "GroupC"). I would expect rows 5 & 6 to still appear on the report as 2 separate rows).
I've tried to get the right formula, but can't figure out how to filter out rows 2 and 4.
Solved! Go to Solution.
You could create a new calculated table:
NewTable = FILTER ( Table1; ISEMPTY ( INTERSECT ( CALCULATETABLE ( DISTINCT ( Table1[Group] ); Table1[Username] = EARLIER ( Table1[Username] ); ALL ( Table1 ) ); { "GroupA"; "GroupB" } ) ) )
Another option, depending on what you want, is to use a table visual with Username and Group in values (with "Don't summarize") and then create a measure as indicated below. Then place the new measure in visual level filters of the table visual and select 'Show items when the value is' --> 1
ShowMeasure = IF ( ISEMPTY ( INTERSECT ( CALCULATETABLE ( DISTINCT ( Table1[Group] ); ALL ( Table1[Group] ) ); { "GroupA"; "GroupB" } ) ); 1 )
You could create a new calculated table:
NewTable = FILTER ( Table1; ISEMPTY ( INTERSECT ( CALCULATETABLE ( DISTINCT ( Table1[Group] ); Table1[Username] = EARLIER ( Table1[Username] ); ALL ( Table1 ) ); { "GroupA"; "GroupB" } ) ) )
Another option, depending on what you want, is to use a table visual with Username and Group in values (with "Don't summarize") and then create a measure as indicated below. Then place the new measure in visual level filters of the table visual and select 'Show items when the value is' --> 1
ShowMeasure = IF ( ISEMPTY ( INTERSECT ( CALCULATETABLE ( DISTINCT ( Table1[Group] ); ALL ( Table1[Group] ) ); { "GroupA"; "GroupB" } ) ); 1 )
Thanks - I went with the measure, which works perfectly.
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |