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 Pu...
"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] )
        )
    )
)

     

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.

    • memoalkatib1's avatar
      memoalkatib1
      Frequent Visitor
      3 years ago

      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.