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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 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.