Forum Discussion

nutmegs's avatar
nutmegs
New Member
5 years ago
Solved

Rank with multiple fields

I have a ranking problem.  I need to rank results by alias, ordered by orderid Positive Result Reporting Table = SUMMARIZE( Data_File, Data_File[RESULT ], Data_File[ALIAS ], Data_File[ORDERID ],...
Need Help
  • smpa01's avatar
    smpa01
    5 years ago

    nutmegsnot sure if you want a calculated column or a measure, so did both.

    Column =
RANKX (
    FILTER ( ranking, EARLIER ( ranking[ALIAS] ) = ranking[ALIAS] ),
    ranking[ORDERID],
    ,
    ASC
)

Measure:= 
RANKX (
    FILTER ( ALLSELECTED ( ranking ), ranking[ALIAS] = MAX ( ranking[ALIAS] ) ),
    CALCULATE ( MAX ( ranking[ORDERID] ) ),
    ,
    ASC
)

     

     

     

  • smpa01's avatar
    smpa01
    5 years ago

    nutmegs 

    Measure = 
RANKX (
    FILTER (
        ALLSELECTED ( 'rank' ),
        'rank'[ALIAS] = MAX ( 'rank'[ALIAS] )
            && 'rank'[TestDate] = MAX ( 'rank'[TestDate] )
    ),
    CALCULATE ( MAX ( 'rank'[ORDERID] ) ),
    ,
    ASC
)
smpa01's avatar
smpa01
Community Champion
5 years ago

nutmegscan you give a sample data for Data_File?

nutmegs's avatar
nutmegs
New Member
5 years ago

Unlike Tableau I can't attach anything.  I  am going to paste the data from a sample csv.  Just paste it into notepad and save as csv.  The Test Order field is the calculated field (rank) based on the order of the orderids grouped by alias.  I am attaching what I want to see (Test order is ranked 1,2,3 for each of these alias)

 

ALIAS,ORDERID,UM_RESOURCE,RESULT,Test Order
1,1,8800,Not Detected,1
1,12,8800,Not Detected,2
1,22,8800,Not Detected,3
2,2,8800,Not Detected,1
2,13,8800,Not Detected,2
2,23,8800,Not Detected,3
3,3,8800,Not Detected,1
3,14,8800,Not Detected,2
3,24,8800,Not Detected,3

 

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago

    nutmegsnot sure if you want a calculated column or a measure, so did both.

    Column =
RANKX (
    FILTER ( ranking, EARLIER ( ranking[ALIAS] ) = ranking[ALIAS] ),
    ranking[ORDERID],
    ,
    ASC
)

Measure:= 
RANKX (
    FILTER ( ALLSELECTED ( ranking ), ranking[ALIAS] = MAX ( ranking[ALIAS] ) ),
    CALCULATE ( MAX ( ranking[ORDERID] ) ),
    ,
    ASC
)

     

     

     

    • nutmegs's avatar
      nutmegs
      New Member
      5 years ago

      OK. So the meaure works, but I actually need them ranked by date and alias.  I've attached the data for a csv that has the expected result with a new date field.

       

      ALIAS,ORDERID,UM_RESOURCE,RESULT,TestDate,Test Order
      1,1,8800,Not Detected,1/1/2021,1
      1,12,8800,Not Detected,1/2/2021,1
      1,22,8800,Not Detected,1/2/2021,2
      2,2,8800,Not Detected,1/4/2021,1
      2,13,8800,Not Detected,1/5/2021,2
      2,23,8800,Not Detected,1/6/2021,3
      3,3,8800,Not Detected,1/7/2021,1
      3,14,8800,Not Detected,1/7/2021,2
      3,24,8800,Not Detected,1/8/2021,1

       

      This picture is an example of what I want the Test Order measure to produce.

       

      I tried this and it did not produce the expected result.  

      Test Order = calculate (RANKX (
      FILTER ( ALLSELECTED ( 'Positive Result Reporting Table' ), 'Positive Result Reporting Table'[ALIAS] = MAX ( 'Positive Result Reporting Table'[ALIAS]) ),
      CALCULATE ( MAX ( 'Positive Result Reporting Table'[ORDERID] ) ),
      ,
      ASC
      ) , ALLEXCEPT('Positive Result Reporting Table','Positive Result Reporting Table'[TestDate]))

       

      • smpa01's avatar
        smpa01
        Community Champion
        5 years ago

        nutmegs 

        Measure = 
RANKX (
    FILTER (
        ALLSELECTED ( 'rank' ),
        'rank'[ALIAS] = MAX ( 'rank'[ALIAS] )
            && 'rank'[TestDate] = MAX ( 'rank'[TestDate] )
    ),
    CALCULATE ( MAX ( 'rank'[ORDERID] ) ),
    ,
    ASC
)