Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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])))
Solved! Go to 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
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:
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
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |