Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

 

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

12 REPLIES 12
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

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.

Anonymous
Not applicable

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

 

 

Hi @Anonymous ,

 

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!!
Anonymous
Not applicable

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

Hi @Anonymous ,

 

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

 

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

 

Regards,

Harsh Nathani

Anonymous
Not applicable

Hi @harshnathani,

 

thanks for the quick reply. I'm talking about the rankx formula. 

 

I created the table: Report_Table

Report_Table = SUMMARIZE(table1;table1[customer];table1[material];table1[Order_date])

 

Ranking1(calculated column):

Ranking1 =

var a = MAX(Report_Table[customer])
var b = MAX(Report_Table[Material])

return

RANKX(FILTER(ALL(Report_Table[Soldto_number];Report_Table[Material_number];Report_Table[Order_date]); Report_Table[customer] = a && Report_Table[customer] = b); CALCULATE(MAX(Report_Table[Order_date]));;ASC;Skip)

 

Report_table:

CustomerMaterialOrder_dateRanking1
A1231.2.20191
A1235.2.20191
A1236.3.20191
A1241.2.20191
B12310.4.20191
B12315.6.20191
C1243.8.20191
C12415.9.20191

 

So basically the ranking is not working like that. 

Do you have an idea on that? 

 

BR

Lanko

 

Hi @Anonymous ,

 

 

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)

Hi all, Hi Harsh,

Great formula. Any idea how I can achieve this:

powerbifuddaa_0-1698047358708.png

Thanx for your help!

Regards, Elmer

Anonymous
Not applicable

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

@Anonymous ,

 

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!!
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

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!!
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.