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
joyhackett
Helper II
Helper II

Filter a visual using the selected value from an unrelated table

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.

joyhackett_3-1623723506458.png

 

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)

joyhackett_0-1623725591180.png

 

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.

 

joyhackett_8-1623725139988.png

 

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

joyhackett_4-1623723797224.png

This would be ideal:

joyhackett_6-1623724134517.png

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

joyhackett_7-1623724302572.png

 



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
 
1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
joyhackett
Helper II
Helper II

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!

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.