topic Re: Filter transactions while RANKX in DAX Commands and Tips

Filter transactions while RANKX

Anonymous — Mon, 10 May 2021 18:15:37 GMT

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,

Re: Filter transactions while RANKX

daxer-almighty — Mon, 10 May 2021 18:54:08 GMT

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.

Re: Filter transactions while RANKX

Anonymous — Mon, 10 May 2021 19:05:24 GMT

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