Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
While ranking using RANKX, is it posible to filter the transaction based on some criteria ?
I created a measure like this :
Rank =
VAR tmpVal = SUM('Transactions'[Item qty])
RETURN
IF(tmpVal = 0, BLANK(),
RANKX(
ALL('Products'[Item id]),
CALCULATE(
SUM('Transactions'[Item qty]),
'Products'[Product type] = 1
)
)
)
The explanation to that, I put a filter inside the CALCULATE, for the transactions only for product with type = 1, which just some category inside my Product master.
But the result I get now is :
If I remove the filter of product type then it is correct.
Should I filter my Product as well ? it is seems odd, because if the transactions already filter, why they need to rank products which not in the transactions anyway.
Thanks,
You're not ranking transactions but products. And products have the types you mention, not transactions. It's not entirely clear what you're after since the description is misleading. Would you care to show what the correct output should look like, please? And please try to clarify your description.
Hi @daxer-almighty ,
I'm sorry, may I ask which one is not clear ? Maybe what a bit confusing is between "item id" and "product type code" ? item id is same like "product id", while "product type code" is another field inside my Product master, which use for a category, and right now I only want to rank products with type code =1 within my transactions.
For the correct / expected output, I think can refer to the image, I've sort the column "Item qty", so the rank should follow that order.
However, I tried to change the measure, to filter the Product instead like this :
Rank =
VAR tmpVal = SUM('Transactions'[Item qty])
RETURN
IF(tmpVal = 0, BLANK(),
RANKX(
FILTER(
ALL('Products'),
'Products'[Product type code] = 1
),
CALCULATE(
SUM('Transactions'[Item qty])
),,DESC,Dense
)
)
It looks "correct" a bit, only there is one double rank like below:
Thanks