RANKX is ranking blanks, ALLSELECTED doing as it should?

[Anonymous]

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?

[nvprasad · Accepted solution]

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,
N V Durga Prasad

[Anonymous]

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. 

[nvprasad · Accepted solution]

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,
N V Durga Prasad

[Anonymous · Accepted solution]

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