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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
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

imani_tech's avatar
imani_tech
Frequent Visitor
8 years ago

Thank you so much!!!