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

## How to assign Rank for zero but exclude null or blank values

Hi,
I have the following tables
Dim :

 LocationCodeName Locationcode A A001 B B001 C C001 D D001 E E001 F F001

SalesFact :

 Locationcode Qty A001 8 B001 20 E001 60 F001 40 C001 22

Target:

 Locationcode Target A001 10 B001 20 E001 30 F001 40 C001 20

Target % = Divide(Sales- Target , Target) *100
So i need to calculate rank as follows:

 LocationCodeName Qty Target Target % Rank A 8 10 -20 4 B 20 20 0 3 C 22 20 10 2 D E 60 30 100 1 F 40 40 0 3

Rank should be given based on highest to lowest target% including 0 (which means the location has met its target completely - Location B001 and F001 in this case) however excluding non-existing location (means no target was given to these location in this month - Location D001 in this case).

I tried the following DAX formula but it doesnt work for null values (where there was no target given):

Location Ranking =
RANKX(All('dim'[LocationCodeName])
,
CALCULATE( [# Target %]),,desc,dense
)
but this is giving rank to blank and 0 as same rank.

1 ACCEPTED SOLUTION
Super User
4 REPLIES 4
Super User

Frequent Visitor

It's working perfectly. Really appreciate it.

Also, I found another way to resolve this :

if(NOT(ISBLANK([Target %])), rankx(FILTER(ALL('dim'[LocationCodeName]),NOT(ISBLANK([Target]))),CALCULATE([Target %]),,,Dense),BLANK())
Super User

If you are interested you can run both versions of this through DAX Studio and compare the query plans and server timings.

CALCULATE([Target %])  seems redundant.

Frequent Visitor

Yeah, you are right @lbendlin . Much appreciated. Thanks again 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors