Using RANKX within a category

[axelf24]

Hello, I am trying to get the correct ranking of values within a category. I attach the details below and would be grateful if you could help here. I have tried several attempts using Google etc but to no avail. Thank you.

[Anonymous · Accepted solution]

Hi @axelf24 ,

@efebent & @DataNinja777 , thanks for your concern about this case. I tried to create a sample data myself based on @axelf24's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

1\My data source-'Table'

2\ Create a measure 

Min Difference Per ID = 
MINX(
    FILTER(
        ALL('table'),
        'table'[ID] = MAX('Table'[ID])
    ),
    'Table'[Dates Difference]
)

3\Result

Best Regards,

Bof

[DataNinja777]

Hi @axelf24 ,

For the case described above, 

1. First adjust the slicer date logic to ensure the correct date range is used.

Slicer Date =
MAXX(
    FILTER(
        ALL('table'),
        [Reference] = MAX([Reference]) &&
        [Processing Date] > MIN('all Date'[Date]) &&
        [Processing Date] <= MAX('all Date'[Date])
    ),
    [Processing Date]
)

Then, rank the differences within each ID and ensure that the ranking is calculated over the correct context. However, you may want to use minx instead of rankx to directly get the minimum value.

Min Difference Per ID =
MINX(
    FILTER(
        ALL('table'),
        'table'[ID] = EARLIER('table'[ID])
    ),
    'table'[Difference]
)

Best regards,

[axelf24]

Thanks for the quick reply.

Unfortunately when I use the code to create Min Difference Per ID, I get the error message

which I don't quite understand.

[DataNinja777]

Hi @axelf24 ,

What about modifying the dax as shown below?

Min Difference Per ID =
CALCULATE(
    MIN('table'[Difference]),
    FILTER(
        'table',
        'table'[ID] = EARLIER('table'[ID])
    )
)

 Best regards,

[axelf24]

Still getting error message below (I've just just replaced Table with the actual name and ID with reference):

[efebent]

The error you're encountering occurs when a measure tries to operate directly on a column that has multiple rows, making it impossible to return a single value. In this case, the column contains multiple values, so it's giving a warning that a single date value can't be determined.

To fix this issue, you can revise the formula . and use the MAX function to retrieve the highest value in each row

it should be : EARLIER(MAX(.......))

[axelf24]

Good morning, thanks for your reply as below.

Sorry to chase but asking if you can suggest any other solutions?

Many thanks for your time.

[axelf24]

Many thanks for your reply.

The adjustment suggested still gives the same error -