Dynamic date filters in a calculated table

[Anonymous]

Hi All,

I am trying to create a silcer that allows users to view countries that are in the top 20 by volume. That said, the users can also apply a date range filter (via a minimum and maximum quarter) to the dashboard, which changes which countries appear in the top 20.

I have been able to create a rankx formula that shows the top 20 countries, however, I am unable to use it as a filter on the visuals. Side note - if there is a way to use this dynamix rankx measure as a filter then that would work too. As an alternative, I tried to create a calculated table that contains a list of countries and whether that country is a top 20 based on user filters. I did this to turn the rankx measure into a calculated column.

Issue: When I create that calcuated table or any sort of calcuated value, though, the rank shown is for all dates and ignores user applied filters for dates. I am able to manually assign the dates, however, when I create a variable for the dates it gives me the values for all dates.

In short, this sort of works (at the expense of hard coding the dates, which I can't do):

CalculatedTable =

    ADDCOLUMNS(

        SUMMARIZE('Country Hierarchy',

                'Country Hierarchy'[Country/Region],

"Volume Summarized",CALCULATE('Country Volume'[Sum Volume],FILTER('Country Volume','Country Volume'[Date]>= DATE(2020,1,1) && 'Country Volume'[Date]<=DATE(2020,3,31)))),

"Rank",RANKX(ALL('Country Hierarchy'),[Volume Summarized],,DESC,Dense) ,

                "Category", if([Rank]<=20, "Top 20"))

But this does not:

CalculatedTable =

    ADDCOLUMNS(

        SUMMARIZE('Country Hierarchy',

                'Country Hierarchy'[Country/Region],

"Volume Summarized",CALCULATE('Country Volume'[Sum Volume],FILTER('Country Volume','Country Volume'[Date]>= FIRSTDATE('Date'[Start Of Quarter]) && 'Country Volume'[Date]<=LASTDATE('Date'[End Of Quarter])))),

"Rank",RANKX(ALL('Country Hierarchy'),[Volume Summarized],,DESC,Dense) ,

                "Category", if([Rank]<=20, "Top 20"))

Does anyone know why the above happens? Thanks so much!

[Anonymous]

Hi! Thanks so for the reply!

While that method does work on a number of visuals, it needs to be applied across the entire report (which includes multiple visuals). When I do that for some of those visuals, the dropdown arrow to select less than 21 doesn't open. Specfically, it won't work on summarized visuals that group all of the countries together, such as a KPI visual. 

I'll add that I understand how to create a switch statement of sorts in the measures of those KPI visuals that can toggle on and off the top 20 filter just for that measure. That said, I have countless measures I'd need to do that to and I'm worried it'll be very processing heavy and slow the report down. I was hoping the calculated table could prevent me from having to do that.

Do you know if there is a different way to apply it to those summarized visuals or am I looking at it the wrong way?

Thanks so much!!

[Anonymous]

Hi @Anonymous ,

According to your statement, I know that, you currently want to get TopN countries which are sorted ​by sum of volume filtered by date slicer. Mostly , we will use  Vera_33 's method to create a rank measure ,then add this measure into your visual level filter and set it to show items when value <=20.

Currently, you have much visuals, so this method should not be a good. You don't want to add measures as filter again and again. However, we couldn't add measure as a filter into filters except visual level filter.

And slicer couldn't filter calcualted table. The best way is to create a summarize table with Top sum values you need. I think your code for calcualted table is correct. You can change the dates in filter in your code to update your calcualted table.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33]

Hi @Anonymous 

I don't have enough context on your model and your visuals /  measures, would you like to send over a sample .pbxi file? I will pm you my email.

[Vera_33]

Hi @Anonymous 

I am not sure why you are creating a calculated table, and trying to pass the date filters...if your model is 1:N relationship like below and you try to display TOP 20 countries in a time period regardless their region which means comparing volume against all countries, then you can use your RANKX as a filter in Filters Pane -> Filters on this visual -> less than 21

Country Hierarchy: dim table

Date: dim table

Country Volume: fact table

RANKX(ALL('Country Hierarchy'),[Volume Summarized],,DESC,Dense) 

Otherwise please provide sample data and expected result