cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

Data puzzle with great value!

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

1 ACCEPTED SOLUTION
Super User

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
)``````
2 REPLIES 2
Super User

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
)``````
New Member

Thank you very much! If I could give you a hug, I would.

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors