cancel
Showing results for
Did you mean:
Anonymous
Not applicable

Rankx with Dates

Hi all,

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

 Customer Material Order Date RankX A 123 1.1.2019 1 A 123 1.1.2019 2 A 123 3.3.2019 3 A 145 10.1.2019 1 A 186 5.1.2019 1 A 186 7.1.2019 2 B 123 20.1.2019 2 B 123 9.1.2019 1

Can somebody help me with that?

Thanks

Christoph

1 ACCEPTED SOLUTION
Community Champion

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)

Regards,
Harsh Nathani

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

11 REPLIES 11
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)

 Customer Material Order Date A 123 1.1.2019 A 123 2.2.2019 A 123 3.3.2019 A 145 10.1.2019 A 145 12.1.2019 A 186 7.1.2019 B 123 20.1.2019 B 123 9.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

Community Champion

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)

Thanks and Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Anonymous
Not applicable

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

Community Champion

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

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:

 Customer Material Order_date Ranking1 A 123 1.2.2019 1 A 123 5.2.2019 1 A 123 6.3.2019 1 A 124 1.2.2019 1 B 123 10.4.2019 1 B 123 15.6.2019 1 C 124 3.8.2019 1 C 124 15.9.2019 1

So basically the ranking is not working like that.

Do you have an idea on that?

BR

Lanko

Community Champion

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)

Regards,
Harsh Nathani

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

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?

 Customer Material Order Date MeasureARankx(based on your filter formula) MeasureBSumx(based on your filter formula) MeasureCVar.P("on MeasureA")based on A 123 1.1.2019 A 123 10.1.2019 B ... ... B C C

Thanks

lanko

Community Champion

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

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!