Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous,

       

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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)