Forum Discussion

imani_tech's avatar
imani_tech
Frequent Visitor
8 years ago
Solved

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...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    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. :smileyhappy:

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

     

    Regards

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Sure it does.

 

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