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.
I have a table with two columns: User and Delegate. Each column contains ID-type values. I'm trying to figure which Users are also Delegates. Here is how I would do it in SQL:
SELECT DISTINCT
u.[User],
d.Delegate
FROM [Sheet1$] u
INNER JOIN [Sheet1$] d
ON u.[User] = d.Delegate
I'm new to Power BI and DAX, so I have no idea how to accomplish this goal using those tools. All I know is Power BI/DAX does not support self joins.
What approach should I take?
Solved! Go to Solution.
Hi @imani_tech,
If I understand you correctly, you should also be able to use DAX to create a calculate column in your 'Sheet1$' table to indicate if the User/Delegate are both User and Delegate. Then you can use the new created calculate column as Slicers or Visual/Page/Report level filters on your report. The formula below is for your reference.
IsDelegateUser = IF ( NOT ( ISBLANK ( LOOKUPVALUE ( 'Sheet1$'[Delegate], 'Sheet1$'[Delegate], 'Sheet1$'[User] ) ) ) || NOT ( ISBLANK ( LOOKUPVALUE ( 'Sheet1$'[User], 'Sheet1$'[User], 'Sheet1$'[Delegate] ) ) ), 1, 0 )
Regards
Hi @imani_tech,
If I understand you correctly, you should also be able to use DAX to create a calculate column in your 'Sheet1$' table to indicate if the User/Delegate are both User and Delegate. Then you can use the new created calculate column as Slicers or Visual/Page/Report level filters on your report. The formula below is for your reference.
IsDelegateUser = IF ( NOT ( ISBLANK ( LOOKUPVALUE ( 'Sheet1$'[Delegate], 'Sheet1$'[Delegate], 'Sheet1$'[User] ) ) ) || NOT ( ISBLANK ( LOOKUPVALUE ( 'Sheet1$'[User], 'Sheet1$'[User], 'Sheet1$'[Delegate] ) ) ), 1, 0 )
Regards
Thank you so much!!!
Sure it does.
let Source = Table.NestedJoin(Table5,{"User"},Table5,{"Delegate"},"Table5",JoinKind.Inner), #"Removed Columns" = Table.RemoveColumns(Source,{"Delegate", "Table5"}) in #"Removed Columns"
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |