Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter two unrelated table

 

I have two unrelated tables (User and Customer) as shown above. I wish to select a user(on third visual) such that the Customer table displays rows only where User Region = Customer Region. Alternatively, I wish to generate a 'Yes' and "No" flag in the Customer table whenever a user is selected in the User table based on the same condition. Please note these two table must remain unrelated.

Thank you

Need Help

3 Replies

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

    Anonymous see the attached solution, it also takes care if no region or more than one region is selected. Tweak it as you see fit.

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you for the response. Can you please explain why this couldn't be accomplished with calculated column ?

    Here, I wish to show 'Yes' for all customers whenever a 'Global' User is selected which in this sample data would be Zeta. How to accomodate that ?

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

    Anonymous here is the updated measure to get Yes if Global is selected:

     

    Yes/No = 
VAR __selectedRegions = VALUES ( User[User Region] )
VAR __customRegion = CALCULATE ( COUNTROWS ( Customer ), INTERSECT ( VALUES ( Customer[Customer Region] ), __selectedRegions ) )
VAR __isGlobalSelected = MAXX ( FILTER ( __selectedRegions, [User Region] = "Global" ), [User Region] )
RETURN
IF ( NOT ISBLANK ( __customRegion ) || __isGlobalSelected = "Global", "Yes", "No" )

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡