Rank filtered date

[LuisLOCapelari]

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]

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]

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]

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]

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/EZ3q44p...  

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

[v-yueyunzh-msft · Accepted solution]

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]

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]

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