Forum Discussion

LuisLOCapelari's avatar
LuisLOCapelari
Helper I
3 years ago
Solved

Rank filtered date

I need help making a table or column that works similarly to RANKX, which indicates the order of dates in a table of dates, but that rank is reset according to the date selected in a slicer. For exa...
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , LuisLOCapelari 

    Thanks for your quick response and your sharing sample pbix file to me!

    For your need , you want to add the dynamic rank in the x-axis along with the Quarter and Month.

    Here are the steps you can refer to :
    (1)First , we also need to create a dimension table using the "Numetric range":

    (2)We can create two measures like this:

    Measure = 
 var _t=SELECTCOLUMNS( ALLSELECTED('Calendar') , "Date" , [Date])
 var _cur_date =  MAX('Calendar'[Date])
 return
 if(_cur_date in _t , RANKX( ALLSELECTED('Calendar') , CALCULATE(MAX('Calendar'[Date])) , ,ASC,Dense), BLANK())

    Measure 2 = var _t=ADDCOLUMNS( ALLSELECTED('Calendar') , "measure_rank" , [Measure],"value", CALCULATE(SUM('Sales'[Value])))
var _xaxis = [Parameter Value]
var _dates =  VALUES('Calendar'[Date])
var _t2 = FILTER(_t , [Date] in _dates)
return
IF( ISINSCOPE('Parameter'[Parameter]),MAXX(FILTER(_t2 , [measure_rank] = _xaxis) , [value]) , SUM('Sales'[Value]))

     

    (3)Then we can put the fields on the visual and we can get the result as follows:

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

     

LuisLOCapelari's avatar
LuisLOCapelari
Helper I
3 years ago

Hello. Thanks a lot for your help, this is exactly what I wanted. It worked in my project.

I'll take it as an answer, but just one last request. Could you give a brief explanation of what each measure does, please?

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi, LuisLOCapelari 

Thanks for accepting my post as the solution first!

For the two measures, the first measure is used to make the rank for the date , we can put the measure in the table to explain :

The second measure is used to return the sum of the value according to the parameter value in current Quarter and Month.

Measure 2 = var _t=ADDCOLUMNS( ALLSELECTED('Calendar') , "measure_rank" , [Measure],"value", CALCULATE(SUM('Sales'[Value])))
var _xaxis = [Parameter Value]
var _dates =  VALUES('Calendar'[Date])
var _t2 = FILTER(_t , [Date] in _dates)
return
IF( ISINSCOPE('Parameter'[Parameter]),MAXX(FILTER(_t2 , [measure_rank] = _xaxis) , [value]) , SUM('Sales'[Value]))

The first var _t = ...  is used to create a virtual table to add the [Measure] and [Sum of value] per date.

var _xaxis is used to get the [Parameter] you create .

var _dates is used to get the dates in  in current Quarter and Month's filter context.

var _t2 is used to Filter which dates is in current Quarter and Month, and which  [Parameter] you need to show in the end.

Then in the end , we use the ISINSCOPE() to judge the different hierarchy to return the different value.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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