User = Delegate

[imani_tech]

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?  

[v-ljerr-msft · Accepted 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

[imani_tech]

Thank you so much!!!

[Greg_Deckler]

Sure it does.

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