Filter a visual using the selected value from an unrelated table

[joyhackett]

I am new to DAX, so I hope I explain this well... I'll add pictures.

I have 2 tables ("all_revenue" and "club_revenue") that cannot have a relationship for a multitude of reasons (mainly RLS). Both tables contain a club_key column. There is also a dim_club table that also contains club_key, joined to club_revenue.

The "all_revenue" matric is the star of our Comparison Dashboard. It is summarized to calculate each club_key's rank and percentile rank. (calcs at bottom of post)

We can only display the row values for the club_key that is selected as an all-page filter. So if the user selects club_key = 269 from dim_club, only the row where all_revenue[club_key] = 269 will be included in the visualization. 

So basically, I don't want to filter all_revenue's data, just what is included in the visualization.

Is there a way to pass the selected values of the dim_club club_key parameter that is being used as an all-page filter into the visualization filter as the all_revenue club_key value?

I was thinking of using an IF measure to use as a filter: 

club_key_match = IF(all_revenue[all_revenue.club_key] = SELECTEDVALUE(dim_club[club_key]),1,0)

but something is wrong with that because the club_key_match value for club_key 0 = 69799. I'm expecting the value to be 1 for club_key 269 (the selected value) and all other rows to be 0.

This is how it is now (without sharing the club_key selection):

This would be ideal:

If I create a relationship between dim_club and all_revenue on club_key, all of the measures break:

Thank you in advance for any help!

The current multi-row card and matrix DAX:
Total Amount = sum(all_revenue[amount])

Club Count = CALCULATE(DISTINCTCOUNTNOBLANK(all_revenue[club_key]),REMOVEFILTERS(all_revenue[club_key]))

Rank = RANKX(ALL(all_revenue[club_key]),CALCULATE(sum(all_revenue[amount])))

Perc Rank = DIVIDE(([Club Count]-[Rank]),[Club Count]-1)*100

[joyhackett]

I guess sometimes it just helps to write it all out.

I was able to come up with a not-so-great-but-works solution. I changed my Total Amount measure to:

Total Amount = CALCULATE(sum(all_revenue[amount]),FILTER(all_revenue,all_revenue[all_revenue.club_key] = SELECTEDVALUE(dim_club[club_key])))

Then, I set the filter on each visualization to [Total Amount] Is Not Blank.

It's working ? but if there is a better way, I would still very much appreciate the opportunity to learn. Thanks!

[Anonymous · Accepted solution]

Hi @joyhackett,

Yes, this is the general process to handle filters from unrelated table fields.

BTW, I'd like to suggest you use 'VALUES' fucntion instead of SELECTEDVALUE, it not suitable to handle multiple selections. (SELECTEDVALUE function will return blank if you selected multiple values)

SELECTEDVALUE function - DAX | Microsoft Docs

Total Amount =
CALCULATE (
    SUM ( all_revenue[amount] ),
    FILTER (
        all_revenue,
        all_revenue[all_revenue.club_key] IN VALUES ( dim_club[club_key] )
    )
)

Regards,

Xiaoxin Sheng