Forum Discussion

BenazirMohammad's avatar
BenazirMohammad
Helper I
6 years ago
Solved

CONDITIONAL JOIN

Hi, I am fairly new to this and was wondering if anyone can help with this. I am not sure how to do a conditional JOIN in DAX or power query.   Here is my SQL statement. Select t1.* ,t2.col4 ,t3....
Need Help
BenazirMohammad's avatar
BenazirMohammad
Helper I
6 years ago

Thanks,
I am trying to setup RLS.
Basically, I would like CATEGORY_KEY from Table 1 based on what user have access to in Table 2.

 

Table1 (T1). This is a Master table with full list of Region, Class, MFR.

CATEGORY_KEY REGION_KEY CLASS_KEY MFR_KEY
23361 1 75 5
23362 2 75 8
23363 1 75 106
23364 1 75 256
23365 1 75 465
23366 1 75 600
23367 1 75 677
23368 1 75 738
23369 1 75 770
23370 2 20 868
23371 3 75 2320

 

Table2 (T2). This a configuration table that shows which user is supposed to see what.
It could be a combination of Region and/or Class and/or MFR.
-99 means ALL.
Example:
User1: has access to ALL Regions, Specific Class and ALL MFR.
User 2: has access to specific Region, ALL Class and specific MFR
User5: has access to ALL Region, ALL Class, CLL MFR.


User REGION_KEY CLASS_KEY MFR_KEY
User1 -99 75 -99
User1 -99 77 -99
User1 -99 78 -99
User2 3 -99 2320
User2 3 -99 2592
User3 -99 -99 2320
User3 -99 -99 2581
User3 -99 -99 2584
User4 2 11 -99
User4 2 18 -99
User4 2 20 -99
User5 -99 -99 -99

 

Table (T3). Its just a table with User and Email
User EMail
User1 [email protected]
User2 [email protected]
User3 [email protected]
User4 [email protected]
User5 [email protected]


Expected Result:


User1 Expected result
CATEGORY_KEY REGION_KEY CLASS_KEY MFR_KEY
23361 1 75 5
23362 2 75 8
23363 1 75 106
23364 1 75 256
23365 1 75 465
23366 1 75 600
23367 1 75 677
23368 1 75 738
23369 1 75 770
23371 3 75 2320

User2 Expected result
CATEGORY_KEY REGION_KEY CLASS_KEY MFR_KEY
23371 3 75 2320


User5 Expected result
CATEGORY_KEY REGION_KEY CLASS_KEY MFR_KEY
23361 1 75 5
23362 2 75 8
23363 1 75 106
23364 1 75 256
23365 1 75 465
23366 1 75 600
23367 1 75 677
23368 1 75 738
23369 1 75 770
23370 2 20 868
23371 3 75 2320

 

I hope this makes sense

 

Thanks

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi  BenazirMohammad 

Just try this formula to create a new table:

Table = 
VAR _table1jiontable2 =
    FILTER (
        GENERATE (
            SELECTCOLUMNS (
                Table2,
                "User", [User],
                "_REGION_KEY", [REGION_KEY],
                "_CLASS_KEY", [CLASS_KEY],
                "_MFR_KEY", [MFR_KEY]
            ),
            Table1
        ),
        ( [_REGION_KEY] = -99
            || [_REGION_KEY] = [REGION_KEY] )
            && ( [_CLASS_KEY] = -99
            || [_CLASS_KEY] = [CLASS_KEY] )
            && ( [_MFR_KEY] = -99
            || [_MFR_KEY] = [MFR_KEY] )
    )
RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS (
            _table1jiontable2,
            "User", [User],
            "REGION_KEY", [REGION_KEY],
            "CLASS_KEY", [CLASS_KEY],
            "MFR_KEY", [MFR_KEY]
        ),
        "EMail", LOOKUPVALUE ( Table3[EMail], Table3[User], [User] )
    )

Result:

User1User5

 

and here is sample pbix file, please try it.

 

Regards,

Lin