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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nutmegs
New Member

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 ],
Data_File[CE_RESOURCE ],
"Test Order",RANKX(ALL( Data_File[ALIAS ], Data_File[CE_RESOURCE ], Data_File[RESULT ]),SUMX( Data_File, Data_File[ORDERQTY ]), Data_File[ORDERID ],ASC,Dense))
yields:
ALIAS CE_RESOURCE ORDERID RESULT Test Order
274 CL Mole cobas 8800-1 483 Not Detected 2
274 CL Mole cobas 8800-1 473 Not Detected 2
274 CL Mole cobas 8800-1 463 Not Detected 2

Expected result:

ALIAS CE_RESOURCE ORDERID RESULT Test Order
274 CL Mole cobas 8800-1 483 Not Detected 1
274 CL Mole cobas 8800-1 473 Not Detected 2
274 CL Mole cobas 8800-1 463 Not Detected 3

 

What am I doing wrong?

2 ACCEPTED SOLUTIONS

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

 

Capture.PNG

 

Capture.PNG

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

@nutmegs 

Measure = 
RANKX (
    FILTER (
        ALLSELECTED ( 'rank' ),
        'rank'[ALIAS] = MAX ( 'rank'[ALIAS] )
            && 'rank'[TestDate] = MAX ( 'rank'[TestDate] )
    ),
    CALCULATE ( MAX ( 'rank'[ORDERID] ) ),
    ,
    ASC
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

6 REPLIES 6
v-yiruan-msft
Community Support
Community Support

Hi @nutmegs ,

You can create a measure as below:

Test Order =
RANKX (
    ALLEXCEPT ( 'Data_File', 'Data_File'[ALIAS] ),
    CALCULATE ( MAX ( 'Data_File'[ORDERID] ) ),
    ,
    DESC,
    DENSE
)

yingyinr_0-1614846571129.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
smpa01
Super User
Super User

@nutmegscan you give a sample data for Data_File?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

 

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

 

Capture.PNG

 

Capture.PNG

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

example.jpg

 

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

 

@nutmegs 

Measure = 
RANKX (
    FILTER (
        ALLSELECTED ( 'rank' ),
        'rank'[ALIAS] = MAX ( 'rank'[ALIAS] )
            && 'rank'[TestDate] = MAX ( 'rank'[TestDate] )
    ),
    CALCULATE ( MAX ( 'rank'[ORDERID] ) ),
    ,
    ASC
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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