Forum Discussion

memoalkatib1's avatar
memoalkatib1
Frequent Visitor
3 years ago
Solved

RANKx

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


"MEASURE"
"RANKX"
DAX RANKX
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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] )
        )
    )
)

     

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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] )
        )
    )
)

     

    • memoalkatib1's avatar
      memoalkatib1
      Frequent Visitor
      3 years ago

      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's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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