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_Prachi
Frequent Visitor

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

Hi,
I have the following tables
Dim : 

LocationCodeNameLocationcode
AA001
BB001
CC001
DD001
EE001
FF001

 SalesFact : 

LocationcodeQty
A0018
B00120
E00160
F00140
C00122

Target:

LocationcodeTarget
A00110
B00120
E00130
F00140
C00120

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

LocationCodeNameQtyTargetTarget %Rank
A810-204
B202003
C2220102
D    
E60301001
F404003


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. 

Thanks in Advance!
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

4 REPLIES 4
lbendlin
Super User
Super User

lbendlin_0-1694740921177.png

 

Thanks @lbendlin for your response, 
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())

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.

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

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.