Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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] )
)
)
)
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.
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] )
)
)
)
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |