Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Hello! I am new to Power BI and trying to do somethign quite complicated - or so I believe. I have two columns, one with let's say 'numbers X'. I have another column with let's say 'numbers Y'. I would like to return all X values with the same Y value for selected values in column 'numbers X', but not returning a repeat within a selected value from 'numbers X'. The issue I am running into is the lookup function is returning a repeat selected value for itself. See example below. '1' and '2' would be returned because it was found with same 'numbers Y'. '4' would not be returned because 'numbers Y' did not have a repeat value outside of itself.
numbers X | numbers Y | Selected Values (drop down of 'numbers X') | Results ('numbers X' with same values in 'numbers Y') |
1 | 10 | 1 | 1 |
1 | 12 | 2 | 2 |
1 | 10 | 4 | |
2 | 10 | ||
2 | 15 | ||
2 | 13 | ||
3 | 10 | ||
3 | 9 | ||
3 | 6 | ||
4 | 7 | ||
4 | 27 | ||
4 | 9 | ||
4 | 7 |
Solved! Go to Solution.
Hi @ssjordan95
Please find attached sample file with the solution
Filter Measure =
VAR T1 =
VALUES ( 'Table'[numbers Y] )
VAR T2 =
CALCULATETABLE (
VALUES ( 'Table'[numbers Y] ),
EXCEPT ( ALLSELECTED ( 'Table'[numbers X] ), VALUES ( 'Table'[numbers X] ) )
)
VAR T3 =
INTERSECT ( T1, T2 )
RETURN
IF (
NOT ISEMPTY ( T3 ),
1
)
Hi @ssjordan95
Please find attached sample file with the solution
Filter Measure =
VAR T1 =
VALUES ( 'Table'[numbers Y] )
VAR T2 =
CALCULATETABLE (
VALUES ( 'Table'[numbers Y] ),
EXCEPT ( ALLSELECTED ( 'Table'[numbers X] ), VALUES ( 'Table'[numbers X] ) )
)
VAR T3 =
INTERSECT ( T1, T2 )
RETURN
IF (
NOT ISEMPTY ( T3 ),
1
)
Thank you very much! If I could give you a hug, I would.
Check out the May 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
17 | |
15 | |
14 | |
12 | |
11 |
User | Count |
---|---|
11 | |
10 | |
8 | |
7 | |
7 |