Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
snph1777
Helper V
Helper V

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:

 

jill.GIF

 

'All' corresponds to all the segments in the Lookup Table, i.e. X,Y,Z.

 

Lookup Table:

 

jg.GIF

 

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:

 

PBI OLSEN1.GIF

 

 

 

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.

1 ACCEPTED SOLUTION
snph1777
Helper V
Helper V

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

View solution in original post

9 REPLIES 9
snph1777
Helper V
Helper V

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
Community Support
Community Support

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.

v-yangliu-msft_0-1611024530971.png

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

parry2k
Super User
Super User

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

parry2k
Super User
Super User

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

parry2k
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.