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

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