Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
memoalkatib1
Frequent Visitor

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


1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_0-1678251143669.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_0-1678251143669.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Anonymous
Not applicable

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

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 ?

Hi,

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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. 

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.