Power BI DAX : CROSS JOIN between two tables based on a variable filter

[snph1777]

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.

[snph1777 · Accepted 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]
                                     )

[v-yangliu-msft]

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.

[snph1777]

Hello Liu, thanks very much for your help; appreciate it; i have found a simplified solution, and have posted here. agan thanks very much

[parry2k]

@snph1777 sounds good.

[snph1777]

Hello @parry2k       thanks very much for your time and help; i have found a simplified solution and have posted here. Appreciate your help though.

[parry2k]

@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.⚡

[snph1777]

Thanks. Will get back after trying your solution.

[parry2k]

@snph1777 easier would be to create a calculated column by dax if you are ok with it.

[snph1777]

A table output is needed. This table will be used for Row Level Security.