cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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!!!

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors