DAX to replicate TopN filter to make it Dynamic, show Top all time - but by week

mark_endicott — Mon, 04 Dec 2023 12:36:28 GMT

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.

Re: DAX to replicate TopN filter to make it Dynamic, show Top all time - but by week

mark_endicott — Mon, 04 Dec 2023 14:33:00 GMT

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 )

topic Re: DAX to replicate TopN filter to make it Dynamic, show Top all time - but by week in DAX Commands and Tips

DAX to replicate TopN filter to make it Dynamic, show Top all time - but by week

Re: DAX to replicate TopN filter to make it Dynamic, show Top all time - but by week