cancel
Showing results for
Did you mean:
Highlighted
Helper I

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
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)

Thanks and Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Highlighted
Community Champion

## Re: Rankx with Dates

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

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

## Re: Rankx with Dates

For Rank Refer these links- These are one of the best you can refer

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
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

## 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)

 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

Highlighted
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)

Thanks and Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Highlighted
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?

 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

Highlighted
Community Champion

## Re: Rankx with Dates

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!!
Highlighted
Helper I

## Re: Rankx with Dates

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

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

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

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

Top Solution Authors
Top Kudoed Authors