topic Rankx starting at 3 instead of 1 in DAX Commands and Tips

Rankx starting at 3 instead of 1

trishd1223 — Wed, 27 Sep 2023 16:03:13 GMT

Hi, I'm trying to rank a measure in Power BI and for some reason it's starting at 3 instead of 1. Here is my sample data. Formula is below. This formula worked when I used it for $ Sales, but it's not working on $/TDP.

Rank $/TDP THP Items =
IF (
[$ / TDP] <> 0,
RANKX (
FILTER (
ALL(AppendedData[THP Item Mapping.Aggregate] ),
[$ / TDP] <> 0
),
[$ / TDP]))

Thanks.

Re: Rankx starting at 3 instead of 1

nirali_arora — Thu, 28 Sep 2023 03:56:32 GMT

Try this

Rank $/TDP THP Items =

IF (

[$ / TDP] <> 0,

RANKX ( ALLSELECTED(AppendedData[THP Item Mapping.Aggregate]), -- Use ALLSELECTED instead of FILTER and ALL

[$ / TDP],

ASC, -- Rank in ascending order

Dense -- Handle ties by assigning the same rank

)

Re: Rankx starting at 3 instead of 1

trishd1223 — Thu, 28 Sep 2023 13:58:14 GMT

Thank you, this worked!