cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imani_tech
Frequent Visitor

User = Delegate

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?  

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

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. Smiley Happy

IsDelegateUser = 
IF (
    NOT (
        ISBLANK (
            LOOKUPVALUE ( 'Sheet1$'[Delegate], 'Sheet1$'[Delegate], 'Sheet1$'[User] )
        )
    )
        || NOT (
            ISBLANK (
                LOOKUPVALUE ( 'Sheet1$'[User], 'Sheet1$'[User], 'Sheet1$'[Delegate] )
            )
        ),
    1,
    0
)

c1.PNGr1.PNG

 

Regards

View solution in original post

3 REPLIES 3
v-ljerr-msft
Microsoft
Microsoft

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. Smiley Happy

IsDelegateUser = 
IF (
    NOT (
        ISBLANK (
            LOOKUPVALUE ( 'Sheet1$'[Delegate], 'Sheet1$'[Delegate], 'Sheet1$'[User] )
        )
    )
        || NOT (
            ISBLANK (
                LOOKUPVALUE ( 'Sheet1$'[User], 'Sheet1$'[User], 'Sheet1$'[Delegate] )
            )
        ),
    1,
    0
)

c1.PNGr1.PNG

 

Regards

View solution in original post

Thank you so much!!!

Greg_Deckler
Super User IV
Super User IV

Sure it does.

 

let
    Source = Table.NestedJoin(Table5,{"User"},Table5,{"Delegate"},"Table5",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Delegate", "Table5"})
in
    #"Removed Columns"

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.