Forum Discussion
nardtmo
Helper III
Helper IIIRanking issue
Hi everyone,
i have this table with desired ranking output column
rank based on points_SA (desc) and rerank every date change.
dax formula below is working but not giving me the desired output.
note: points_SA is a measure
appreciate the help!! thanks in advance
Rank_SA_pt =
RANKX(
FILTER(
ALL(date),
[Points_SA] <> BLANK() && [Points_SA] <> 0
),
[Points_SA],
, DESC, Dense)
| Market Id | date | Points_SA | Rank | output should be |
| texas | 4/16/2024 0:00 | 1 | 1 | 1 |
| Miami | 4/16/2024 0:00 | 0.7 | 2 | 2 |
| Philadelphia | 4/16/2024 0:00 | 0.7 | 2 | 2 |
| North Carolina | 4/16/2024 0:00 | 0.7 | 2 | 2 |
| New York | 4/16/2024 0:00 | 0.6 | 3 | 5 |
| New England | 4/16/2024 0:00 | 0.6 | 3 | 5 |
| New Jersey | 4/16/2024 0:00 | 0.6 | 3 | 5 |
| Los Angeles | 4/16/2024 0:00 | 0.5 | 4 | 8 |
| Washington DC | 4/16/2024 0:00 | 0.5 | 4 | 8 |
| San Francisco | 4/16/2024 0:00 | 0.3 | 5 | 10 |
| California | 4/16/2024 0:00 | 0.1 | 6 | 11 |
| texas | 4/17/2024 0:00 | 1 | 1 | 1 |
| Miami | 4/17/2024 0:00 | 0.7 | 2 | 2 |
| Philadelphia | 4/17/2024 0:00 | 0.7 | 2 | 2 |
| North Carolina | 4/17/2024 0:00 | 0.7 | 2 | 2 |
| New York | 4/17/2024 0:00 | 0.6 | 3 | 5 |
| New England | 4/17/2024 0:00 | 0.6 | 3 | 5 |
| New Jersey | 4/17/2024 0:00 | 0.6 | 3 | 5 |
| Los Angeles | 4/17/2024 0:00 | 0.6 | 3 | 5 |
| Washington DC | 4/17/2024 0:00 | 0.5 | 4 | 9 |
| San Francisco | 4/17/2024 0:00 | 0.3 | 5 | 10 |
| California | 4/17/2024 0:00 | 0.1 | 6 | 11 |
| texas | 4/18/2024 0:00 | 1 | 1 | 1 |
| Miami | 4/18/2024 0:00 | 0.7 | 2 | 2 |
| Philadelphia | 4/18/2024 0:00 | 0.7 | 2 | 2 |
| North Carolina | 4/18/2024 0:00 | 0.6 | 3 | 4 |
| New York | 4/18/2024 0:00 | 0.6 | 3 | 4 |
| New England | 4/18/2024 0:00 | 0.6 | 3 | 4 |
| New Jersey | 4/18/2024 0:00 | 0.6 | 3 | 4 |
| Los Angeles | 4/18/2024 0:00 | 0.5 | 4 | 8 |
| Washington DC | 4/18/2024 0:00 | 0.5 | 4 | 8 |
| San Francisco | 4/18/2024 0:00 | 0.3 | 5 | 10 |
| California | 4/18/2024 0:00 | 0.2 | 6 | 11 |
2 Replies
- Greg_Deckler
Community Champion
nardtmo Unfortunately you only get the options of Dense or Skip. You might try creating your own custom ranking: (3) To *Bleep* with RANKX! - Microsoft Fabric Community
nardtmothanks Greg. Another pespective have two rankings, determine rank SA pt change in values and set the switch condition.. other words cant figure it out. Appreciate the help
Market Id Rank_MR7_SA Rank_SA_pt if Rank_SA_pt row2=row1 condition Atlanta 1 1 FALSE false keep using col D1 values Miami 2 2 FALSE false keep using col D2 values Philadelphia 3 2 TRUE true and stay true use D2 until false North Carolina 4 2 TRUE true and stay true use D2 until false New Jersey 5 3 FALSE false keep using col D6 values New England 6 3 TRUE true and stay true use D6 until false New York 7 3 TRUE true and stay true use D6 until false Washington DC 8 4 FALSE false keep using col D9 values Los Angeles 9 4 TRUE true and stay true use D9 until false San Francisco 10 5 FALSE false keep using col D10 values Southern California 11 6 FALSE false keep using col D11 values
