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.col5

from

table1 as t1,

table2 as t2,

table3 as t3

where  ( (t1.col1  = t2.col1) OR (t2.col1 = -99) ) 

and ( (t1.col2 = t2.col2) OR (t2.col2 = -99) )

and ( (t1.col3 = t2.col3) OR (t2.col3 = -99) )

and (t2.col4 = t3.col4)

 

Is there a way to do something like this or a work around to get similar results.

 

Thanks

Need Help

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    BenazirMohammad you need to provide sample data and also if there are one to many relationship. Also it will be good to know what you trying to achieve as end result.

    • 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