topic Re: RANKx in DAX Commands and Tips

RANKx

memoalkatib1 — Tue, 07 Mar 2023 23:48:50 GMT

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

Re: RANKx

Jihwan_Kim — Wed, 08 Mar 2023 04:53:02 GMT

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

Re: RANKx

memoalkatib1 — Thu, 09 Mar 2023 20:01:41 GMT

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 ?

Re: RANKx

Jihwan_Kim — Fri, 10 Mar 2023 06:35:05 GMT

Hi,

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

Re: RANKx

memoalkatib1 — Fri, 10 Mar 2023 15:00:09 GMT

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.

Re: RANKx

Anonymous — Wed, 27 Sep 2023 07:08:35 GMT

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