Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
Solved! Go to Solution.
@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
)
Measure =
RANKX (
FILTER (
ALLSELECTED ( 'rank' ),
'rank'[ALIAS] = MAX ( 'rank'[ALIAS] )
&& 'rank'[TestDate] = MAX ( 'rank'[TestDate] )
),
CALCULATE ( MAX ( 'rank'[ORDERID] ) ),
,
ASC
)
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
)
Best Regards
@nutmegscan you give a sample data for Data_File?
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
)
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]))
Measure =
RANKX (
FILTER (
ALLSELECTED ( 'rank' ),
'rank'[ALIAS] = MAX ( 'rank'[ALIAS] )
&& 'rank'[TestDate] = MAX ( 'rank'[TestDate] )
),
CALCULATE ( MAX ( 'rank'[ORDERID] ) ),
,
ASC
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
60 | |
58 |