Forum Discussion
CONDITIONAL JOIN
If so, you may try to use sql custom query to import the data in power bi, here is blog for you refer to:
https://community.powerbi.com/t5/Desktop/Writing-own-sql-queries/td-p/465429
Regards,
Lin
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.
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
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
Thanks so much . It will take me sometime to undestand this since I am just a beginner.
I have couple of other questions:
1) How do I go about using that new table to Join it to my FACT tables. My FACT has Category_KEY.
2) Is my approach correct. are there simpler ways to get similar results. I am open to changing data structure / model of my security table.
Table 1 has close to 500k records, Table 2 about 20k records and my FACT table could go over 2mil records.
Thanks
If so, you may try to use sql custom query to import the data in power bi, here is blog for you refer to:
https://community.powerbi.com/t5/Desktop/Writing-own-sql-queries/td-p/465429
Regards,
Lin