cancel
Showing results for
Did you mean:
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.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors