RANKx

[memoalkatib1]

I have a live connection to a "locked" dataset that sits on top of a SSAS model. so I can only create measures. I have a table that has a bunch of columns including CustomerID , ProductID and Date Purchased along with 20 other columns. I would like to rank by customerID, Product ID based on Date Purchased Descending. This would repeat for each customer . so here how I am picturing the ranking to apply (I'm not displaying this data like this on a visual/dashboard)

Ex : 

CustomerID    ProductID      Date Purchased    Rank
1234                       1                 1-1-2023           1
1234                        1                 1-1-2022          2
1234                        2                 1-1-2019          1
3456                        1                 1-1-2020          1
3456                        1                 1-1-2018          2
3456                        6                 1-1-2020          1

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Rank measure: =
IF (
    HASONEVALUE ( Data[CustomerID] ) && HASONEVALUE ( Data[ProductID] ),
    CALCULATE (
        COUNTROWS (
            SUMMARIZE ( Data, Data[CustomerID], Data[ProductID], Data[Date Purchased] )
        ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            SUMMARIZE (
                ALL ( Data ),
                Data[CustomerID],
                Data[ProductID],
                Data[Date Purchased]
            ),
            ORDERBY ( Data[Date Purchased], DESC ),
            KEEP,
            PARTITIONBY ( Data[CustomerID], Data[ProductID] )
        )
    )
)

[Anonymous]

I Have same scenario,
 I am trying to use this solution
When i tried this i am getting all values in the measure as 1
how can i get correct values

[memoalkatib1]

Thank you @Jihwan_Kim . This works wonderfuly and is exactly what I am looking for. However, it does not seem to work with Direct Query connection. Is that a limitation ? is there an alternative way to calcuate this using Direct Query/ Live connection mode ?

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but I think you can try using other than SUMMARIZE function in the measure.

[memoalkatib1]

The "Window" built in function is not supported with live connection/ direct query. I tried subsituting the window function with summerize and other built in functions, but not geting the right ouput. Are you able to shed some insights to how that can be done ? I would highly appreciate it.