Skip to main content
cancel
Showing results for 
Search instead 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

Reply
BI_Nooby
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
nvprasad
Solution Sage
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)
nvprasad_0-1686069578512.png

 

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

View solution in original post

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

View solution in original post

3 REPLIES 3
BI_Nooby
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. 

nvprasad
Solution Sage
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)
nvprasad_0-1686069578512.png

 

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.