cancel
Showing results 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

Frequent Visitor

## RANKX is ranking blanks, ALLSELECTED doing as it should?

I've soled the issue of blanks ranking by using the following DAX:

TestMeasure = IF(NOT ISBLANK([_SUM_Sales],RANKX(ALLSELECTED('Customers'[Cust_Name]),[_SUM_Sales]))

This is the recommended solution by a thread on this forum.
It works and is great.

However wouldn't this be bad for performance? Instead of telling PBI, please just calculate the Sum of Sales for these customers in my matric visual. We're telling it to calculate the Sum of Sales for ALL my customers, even though it might be blank/0.

Thoughts?
2 ACCEPTED SOLUTIONS
Solution Sage

Hi BI_Nooby,

Good Quesiton and my thought is.....

A simple ranks function using allselected like below to get rank

rankcheck2 = RANKX(ALLSELECTED(Customer[Country]),[M_Sales],,DESC)
then below measure to filter cusotmers as per logic
SalesCheck = IF([M_Sales]>30000000,1,0)

With this approach we are not testing rank for each customer or country as the filter is already applied .

Appreciate a Kudos! ‌‌
If this helps and resolves the issue, please mark it as a Solution! ‌‌

Regards,

Frequent Visitor

Haha I see. While not the most elegant solution, it might indeed be better on performance.

3 REPLIES 3
Frequent Visitor

After some more thinking I believe it can be solved elegantly.

The problem is occurring because my Sales table does not have an active way to filter my customer table.

If I can establish a temporary relationship within my measure then I can use my Sales to filter my Customers.

I'll come back to this idea. For now I'll accept a solution.

Solution Sage

Hi BI_Nooby,

Good Quesiton and my thought is.....

A simple ranks function using allselected like below to get rank

rankcheck2 = RANKX(ALLSELECTED(Customer[Country]),[M_Sales],,DESC)
then below measure to filter cusotmers as per logic
SalesCheck = IF([M_Sales]>30000000,1,0)

With this approach we are not testing rank for each customer or country as the filter is already applied .

Appreciate a Kudos! ‌‌
If this helps and resolves the issue, please mark it as a Solution! ‌‌

Regards,

Frequent Visitor

Haha I see. While not the most elegant solution, it might indeed be better on performance.