Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to replicate the function of a TopN visual level filter, so I can use it with a What If parameter to make it dynamic for the users of a report.
I'd like to show the TopN of an item all time, but then break this down by the value that item acheived each week. I have tried several options, but I cannot seem to get it to ignore the date filter to find "all time" then re-apply the date filter to show the top N across time.
Below is an example, where 'MARKET SALES' is my fact table, 'TABLE 1'[TABLE Code] is the item I want to do the TopN for and I'm trying to filter the Calculate table by the whole calendar.
SUMX (
TOPN (
[Top Number Selector Value],
CALCULATETABLE (
SUMMARIZE ( 'MARKET SALES', 'TABLE 1'[TABLE Code] ),
ALL ( 'RLS CALENDAR' )
),
[Total value], DESC
),
[Total value]
)
Unfortunately this presents more items from 'TABLE 1'[TABLE Code] than the number selected in the TOP Number slicer.
Solved! Go to Solution.
I have resoloved this by using a RANKX, inside a calculate that removes the calendar context from RANKX, here's my solution:
VAR Number_to_Rank = [Top Number Selector Value]
VAR Val = [Total value]
RETURN
IF (
CALCULATE (
RANKX ( ALLSELECTED ( 'TABLE 1'[TABLE Code] ), [Total value] ) <= Number_to_Rank,
ALL ( 'RLS CALENDAR' )
),
Val
)
I have resoloved this by using a RANKX, inside a calculate that removes the calendar context from RANKX, here's my solution:
VAR Number_to_Rank = [Top Number Selector Value]
VAR Val = [Total value]
RETURN
IF (
CALCULATE (
RANKX ( ALLSELECTED ( 'TABLE 1'[TABLE Code] ), [Total value] ) <= Number_to_Rank,
ALL ( 'RLS CALENDAR' )
),
Val
)
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |