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 example, if the months February and March are selected, I want this column to say that the first (ordinal) day of the analyzed period is February 1st and that the 30th day is March 2nd, but if January and February are selected, the first day will be January 1st while the 30th day will be February 9th.
The value of that column will be shown on the x-axis of a graph.

  • 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

     

7 Replies

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

    Hi , LuisLOCapelari 

    Accoridng to your description, you want to get a rankx column as the x-axis and also you want to use the slicer to filter the months.

    First of all, if you want to use a slicer to dynamically implement rankx, and also want to place it on the X-axis, neither the calculated column nor the calculated table can meet your needs, and you can only consider using Measure.

    Here are the steps you can refer to :
    (1)This is my test data:

    (2)First , we need to create a measure to get the dynamic rankx in this date table :

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

    We can put it in the table visual to test the result :

    (3)Then if you need put the 1-30 in the x-axis , we need to create a diemsion table :

    And we do not need to make any relationshipo between two tables , and then we can create another measure like this:

    Measure 2 = var _t=ADDCOLUMNS( VALUES('Table'[Date]) , "measure_rank" , [Measure],"value", CALCULATE(SUM('Table'[Value])))
var _xaxis = [Parameter Value]
return
MAXX(FILTER(_t , [measure_rank] = _xaxis) , [value])

     

    Then we can get the result as follows , i test it in the table visual and you can also use this field as x-axis:

     

     

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

      Hello, v-yueyunzh-msft.
      Thanks for taking your time and helping me. His solution was good, he really managed to reset the measurement with each change of month. But that way I couldn't use the measure as the x axis.
      In the line chart I'm using two tables, one with my company's sales and a calendar table, linked by the date in the calendar table and the date of the sale. On the y-axis goes a measure, the sum of sales figures. And on the x-axis there are several columns of the calendar table (year, quarter and month), and I wanted to add this dynamic measure on the x-axis, to be used with drill up and drill down. For me, we can continue using that pbix you sent me, just add a calendar table.

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Community 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