Forum Discussion

LuisLOCapelari's avatar
LuisLOCapelari
Icon for Helper I rankHelper 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

     

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

Hi , LuisLOCapelari 

Thanks for your quick reponse!

According to your description, you currently have a line chart, and you place (year, quarter and month) on the X-axis, and you want to put the value of the dynamic Measure on the X-axis? I don't quite understand what you want in the end.
First of all, if it is a simple measure, it cannot be placed on the x-axis. If you want to place a custom field on the x-axis, you must create a similar 1-n table.

I have created in the previous pbix file and used [Measure 2] to display the dynamic ranking and return the corresponding value.

 

 

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

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
Icon for Helper I rankHelper I
3 years ago

Hello again. Yes, you understood correctly, I want this sequential value of the date to appear on the x-axis, along with the quarter and month, making it possible to change the hierarchy.
I am sending a link to a sample pbix.

 

https://superprofessorcombr-my.sharepoint.com/:u:/g/personal/analytics_superprofessor_com_br/EZ3q44pE811HjchSMkx0keQBWURl6mT4zyHX_7TH7allsw?e=gM9rVC  

 

The expected output would be something similar to the graph below.

 

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    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
      Icon for Helper I rankHelper 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
        Icon for Community Support rankCommunity 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