Forum Discussion

snph1777's avatar
snph1777
Icon for Helper V rankHelper V
5 years ago
Solved

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

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.

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • snph1777's avatar
    snph1777
    5 years ago

    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]
                                         )

9 Replies

  • snph1777's avatar
    snph1777
    Icon for Helper V rankHelper V
    5 years ago

    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]
                                         )

    • snph1777's avatar
      snph1777
      Icon for Helper V rankHelper V
      5 years ago

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      snph1777
      Icon for Helper V rankHelper V
      5 years ago

      Thanks. Will get back after trying your solution.

    • snph1777's avatar
      snph1777
      Icon for Helper V rankHelper V
      5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      snph1777
      Icon for Helper V rankHelper V
      5 years ago

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