Forum Discussion

tndanai's avatar
tndanai
New Member
3 years ago

DAX Measure for HTS Rank: Issue with Rows Having HTS Number for Only One Country

I've created a DAX measure that calculates the rank for a product (HTS Number) across all countries when a country is selected via a slicer. The measure is used in a matrix visualization where each row represents an HTS Number. When no country is selected, I expect the rank column ("HTS Rank in World") to be blank, and it mostly works as expected.

However, when an HTS Number is associated with just one country in the dataset, the measure is returning a rank of 1 even when no country is selected.

Below is the DAX measure I'm currently using:

HTS Rank in World =
VAR CurrentHTSNumber = SELECTEDVALUE(Dictionary[HTS Number])
VAR CurrentCountry = SELECTEDVALUE(Master[Country])
VAR CYValueForCurrentHTSNumber = CALCULATE([CY Value], Master[Country] = CurrentCountry, Dictionary[HTS Number] = CurrentHTSNumber)

RETURN
IF(
ISBLANK(CYValueForCurrentHTSNumber),
BLANK(),
RANKX(
ALL(Master[Country]),
CALCULATE([CY Value], Dictionary[HTS Number] = CurrentHTSNumber),
CYValueForCurrentHTSNumber,
DESC,
Dense
)
)



2 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Icon for Super User rankSuper User
    3 years ago
    tndanai I hope this helps you.
    HTS Rank in World =
    VAR CurrentHTSNumber =
    SELECTEDVALUE ( Dictionary[HTS Number] )
    VAR CurrentCountry =
    SELECTEDVALUE ( Master[Country] )
    VAR CYValueForCurrentHTSNumber =
    CALCULATE (
    [CY Value],
    Master[Country] = CurrentCountry,
    Dictionary[HTS Number] = CurrentHTSNumber
    )
    RETURN
    IF (
    ISBLANK ( CYValueForCurrentHTSNumber ),
    BLANK (),
    RANKX (
    ALL ( Master[Country] ),
    CALCULATE ( [CY Value], Dictionary[HTS Number] = CurrentHTSNumber ),
    DESC,
    DENSE
    )
    )

    • tndanai's avatar
      tndanai
      New Member
      3 years ago

       Thank you for your help. I try the code, but the problem persists.  Seem the function Rankx not working well.