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, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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