cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Rankx with Dates

 

Hi all, 

 

I have the table like that and want to create a report with a RankX

 

 

CustomerMaterialOrder DateRankX
A1231.1.20191
A1231.1.20192
A1233.3.20193
A14510.1.20191
A1865.1.20191
A1867.1.20192
B12320.1.20192
B1239.1.20191

 

Can somebody help me with that? 

 

Thanks

Christoph

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Rankx with Dates

Hi @Lanko ,

 

You can use the below meaures.

 

Ranking =

var _a = MAX(Table1[Customer])
var _b = MAX(Table1[Material])

return

RANKX(FILTER(ALL(Table1[Material],Table1[Customer],Table1[Order Date]), Table1[Customer] = _a && Table1[Material] = _b), CALCULATE( MAX(Table1[Order Date])),,ASC ,Skip)



 
1.jpg
 
 
Thanks and Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Rankx with Dates

@Lanko ,

 

Try 

 

Measure = VARX.P(ALL(Table3),[Ranking])
 
 
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Rankx with Dates

Hi @Lanko ,

 

 

Create a Calculated Column

 

RANK Column = RANKX(FILTER('Table','Table'[Customer] = EARLIER('Table'[Customer]) && 'Table'[Material]= EARLIER('Table'[Material])),'Table'[Order_date],,ASC)
 
 
1.jpg
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

11 REPLIES 11
Highlighted
Community Champion
Community Champion

Re: Rankx with Dates

Hi @Lanko ,

 

Please check if the below measure works.

 

Ranking =
RANKX(
FILTER(
Table3,
Table3[Material] = EARLIER(Table3[Material])),Table3[Order Date],,ASC,Dense)
 
If not, please share some more information on what do you want to rank i.e rank by material, customer. Also, what is the format of your date column.
 
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
 
Highlighted
Super User IV
Super User IV

Re: Rankx with Dates

For Rank Refer these links- These are one of the best you can refer
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Rankx with Dates

Hi @Lanko ,

 

You may create rank using column or measure like DAX below.

 

Column: Rankx = RANKX(FILTER(Table, Table[Customer]=EARLIER(Table[Customer])&&Table[Material]=EARLIER(Table[Material])),Table[Order Date],,ASC ,Skip)


Measure: Rankx = RANKX(FILTER(Table, Table[Customer]=MAX(Table[Customer])&&Table[Material]=MAX(Table[Material])), MAX(Table[Order Date]),,ASC ,Skip)

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

Highlighted
Helper I
Helper I

Re: Rankx with Dates

Hi all, 

 

both solutions are showing different numbers from what I expected.

Let me rephrase my request.

 

I have this table:

 

Format:

Soldto:      123/ABC

Material:    123/ABC

Order date:  Whole number (I displayed the dates in the table below in date format)

 

CustomerMaterialOrder Date
A1231.1.2019
A1232.2.2019
A1233.3.2019
A14510.1.2019
A14512.1.2019
A1867.1.2019
B12320.1.2019
B1239.1.2019

 

I want to create a report, which shows me the following.

A ranking (does need to be with formula rank) like this:

 

I want to see on (Customer & Product) level a ranking of the order dates. 

For example

Customer A with Product 123 for order date: 1.1.2019 = 1

Customer A with Product 123 for order date: 2.2.2019 = 2

Customer A with Product 123 for order date: 3.3.2019 = 3

Customer A with Product 145 for order date: 10.1.2019 = 1

Customer A with Product 145 for order date: 12.1.2019 = 2

...

 

Do you understand what I mean?

 

BR

Lanko

 

 

Highlighted
Community Champion
Community Champion

Re: Rankx with Dates

Hi @Lanko ,

 

You can use the below meaures.

 

Ranking =

var _a = MAX(Table1[Customer])
var _b = MAX(Table1[Material])

return

RANKX(FILTER(ALL(Table1[Material],Table1[Customer],Table1[Order Date]), Table1[Customer] = _a && Table1[Material] = _b), CALCULATE( MAX(Table1[Order Date])),,ASC ,Skip)



 
1.jpg
 
 
Thanks and Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

Highlighted
Helper I
Helper I

Re: Rankx with Dates

Thanks a lot @harshnathani ,

 

I added measureB sumx (Format:numbers) to the report and now I want to add another MeasureC to the report.

 

MeasureC

Shall be the variance (var.p) for every "Customer and Material" combination like the rankx.

Unfortunately var.p needs a column and don't work with measureB.

 

Can somebody help me out here? 

 

 

CustomerMaterialOrder Date

MeasureA

Rankx

(based on your filter formula)

MeasureB

Sumx

(based on your filter formula)

MeasureC

Var.P("on MeasureA")

based on 

A1231.1.2019   
A12310.1.2019   
B......   
B     
C     
C     

 

Thanks

lanko

Highlighted
Community Champion
Community Champion

Re: Rankx with Dates

@Lanko ,

 

Try 

 

Measure = VARX.P(ALL(Table3),[Ranking])
 
 
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

Highlighted
Helper I
Helper I

Re: Rankx with Dates

Hi @harshnathani 

 

power bi is running into performance issues with your nice measure as I want to use that formula in a complexer modell.

 

To receive a better performance, I want to work with tables instead of reports. 

So I created a table with the formula summarize and entered the Columns (Customer, Material, Order Date)

 

Unfortunately your measure is not working in the table. 

 

Could you help me with that? 

 

Many Thanks

Lanko

Highlighted
Community Champion
Community Champion

Re: Rankx with Dates

Hi @Lanko ,

 

Which formula , the rankx formaula or the Var.p formula

 

Also, please share some sample of the table you have created.

 

Regards,

Harsh Nathani

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors