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
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.⚡
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.
A table output is needed. This table will be used for Row Level Security.
User | Count |
---|---|
116 | |
62 | |
59 | |
47 | |
40 |
User | Count |
---|---|
111 | |
64 | |
62 | |
51 | |
48 |