Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have two input tables in my Power BI model.
1) UserPermission
2) Lookup Table
The above two tables are not related.
UserPermission:
'All' corresponds to all the segments in the Lookup Table, i.e. X,Y,Z.
Lookup Table:
UserPermission[SgmtID] is equivalent to LookupTable[SegmentID].
I need to develop an output table, by CROSS JOINing the UserName column
in the UserPermission table, and the Ports column in the Lookup Table.
Desired Output table:
David needs to have all the ports of all the segments in the lookup table (i.e. X,Y,Z).
John needs to have all the ports of segment X only.
Mike needs to have all the ports of segment Y only.
Jill needs to have all the ports of segments X and Z only.
The difference between David and Jill is that David can access all segments, while Jill can access multiple segments, but not all segments.
The output table is needed for Row Level Security (RLS).
I know to use CROSS JOIN using VALUES, also some variables and filters. But not able to develop a full DAX query, due to lack of expertise.
Can anyone kindly help me with this?
Thanks.
Solved! Go to Solution.
Finally figured out: I had to change the column name UserPermissions[SegmentID] to UserPermissions[SgmtID]
Then:
Output Table = FILTER (
SUMMARIZECOLUMNS (
UserPermission[UserName],
UserPermission[SgmtID],
LookupTable[Ports],
LookupTable[SegmentID]
),
[SgmtID] < 0 || [SegmentID] = [SgmtID]
)
Finally figured out: I had to change the column name UserPermissions[SegmentID] to UserPermissions[SgmtID]
Then:
Output Table = FILTER (
SUMMARIZECOLUMNS (
UserPermission[UserName],
UserPermission[SgmtID],
LookupTable[Ports],
LookupTable[SegmentID]
),
[SgmtID] < 0 || [SegmentID] = [SgmtID]
)
Hi @snph1777 ,
Here are the steps you can follow:
1. Create calculated column.
Desired Output table =
var _david=
SELECTCOLUMNS('Table (2)',"UserName","david","Ports",[Ports])
var _john=
SELECTCOLUMNS(FILTER('Table (2)',
[Sement]=CALCULATE(MAX('Table'[Segment]),FILTER('Table',[UserName]="john"))&&
[SementID]=CALCULATE(MAX('Table'[SegmentID]),FILTER('Table',[UserName]="john")))
,"UserName","john","Ports",[Ports])
var _mike=
SELECTCOLUMNS(FILTER('Table (2)',
[Sement]=CALCULATE(MAX('Table'[Segment]),FILTER('Table',[UserName]="mike"))&&
[SementID]=CALCULATE(MAX('Table'[SegmentID]),FILTER('Table',[UserName]="mike")))
,"UserName","mike","Ports",[Ports])
VAR _1=
SELECTCOLUMNS(FILTER('Table','Table'[UserName]="jill"),"1",[Segment])
VAR _2=
SELECTCOLUMNS(FILTER('Table','Table'[UserName]="jill"),"2",[SegmentID])
var _jill=
SELECTCOLUMNS(FILTER('Table (2)',
[Sement] in _1&&
[SementID] in _2)
,"UserName","jill","Ports",[Ports])
return
UNION(_david,_john,_mike,_jill)
2. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Liu, thanks very much for your help; appreciate it; i have found a simplified solution, and have posted here. agan thanks very much
@snph1777 sounds good.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hello @parry2k thanks very much for your time and help; i have found a simplified solution and have posted here. Appreciate your help though.
@snph1777 add the following measure and then a table from this measure
Include =
IF (
SELECTEDVALUE ( User[SegmentId] ) = -1,
COUNTROWS (
CROSSJOIN ( Ports, User )
),
CALCULATE (
COUNTROWS ( Ports ),
TREATAS (
VALUES ( User[SegmentId] ),
Ports[SegmentId]
)
)
)
Here is an expression to create a calculated table
Table = SUMMARIZECOLUMNS('User'[User], 'User'[SegmentId], 'User'[Segment], 'Ports'[Ports], "Include", 'User'[Include])
and this new table will contain your data.
Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks. Will get back after trying your solution.
@snph1777 easier would be to create a calculated column by dax if you are ok with it.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
A table output is needed. This table will be used for Row Level Security.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
108 | |
93 | |
69 |
User | Count |
---|---|
171 | |
135 | |
132 | |
96 | |
94 |