cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Regular 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

Regular 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.

Regular Visitor

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors