Need DAX Rank Fomula to ignore dimension values that are NULL

[tbucki1]

I am trying to write a DAX Rank measure however the rank formula works even when there is no value in my Name (Dimension) column. I need the Rank Formula to ignore null name values... I tried filtering the pivot table (Uncheck blanks), however the rank numbers stay the same. What and I doing wrong....? Here is my sample data...

I have two tables, One Dimension table with car names and ID...second table is my fact table with Car ID and sales amount... Lots of rows in here... over 200K. In the data model, ID from Car_Names Joins to the ID column in the Sales_Table. One to Many....

I have a DAX measure for Sales $: Sales Amount =: Sum('Sales_Table'[Sales])

I need a DAX Rank formula that omits a rank value when null names are in pivot. What am I suppose to do for this?

Table Name = Car_Names

Name           ID

Ford	101
Honda	102
GMC	103
Mercedes	104
Toyota	105
Lexus	106
Infinity	107

Table Name = Sales_Table

ID           Sales

101	$28,345
103	$30,456
104	$12,543
999	$45,623
994	$93,456
992	$23,457
107	$23,419
107	$83,647
106	$12,654
103	$39,571
…	…

Below is a simple pivot Table I built with a RankX formula. Sales Amount is the DAX Measure listed from above...

***NOTE there are some IDs from the Sales_Tables that are not mapped into the Car_Names table so that is why there is a blank value for Names.... Most of the sales $ are unmapped to the DIM Car_Names table and have the highest rank value in the pivot so the Rank formula lables it a "1"... I need it to ignore this row and the $10.1M value...

Current pivot with Rank = Not Correct VIEW

Names	Sales Amount	Rank
	$10,142,576	1
Ford	$6,364,123	2
Honda	$291,435	8
GMC	$3,872,510	4
Mercedes	$5,123,471	3
Toyota	$981,342	5
Lexus	$846,235	6
Infinity	$716,332	7

This is what I want the results to look like.... Correct VIEW

Names	Sales Amount	Rank
	$10,142,576
Ford	$6,364,123	1
Honda	$291,435	7
GMC	$3,872,510	3
Mercedes	$5,123,471	2
Toyota	$981,342	4
Lexus	$846,235	5
Infinity	$716,332	6

I only want the Rank Measure to work on Mapped Text values from the Sales FACT Table.... not NULL Text Values....How would you write this?

[v-shex-msft · Accepted solution]

HI @tbucki1,

You can use the below measure expression to get the rank of the amount that exclude the not matched records:

Measure =
VAR currName =
    SELECTEDVALUE ( T1[Name] )
VAR summary =
    FILTER (
        SUMMARIZE ( ALLSELECTED ( T2 ), [Name], [ID], "total", SUM ( T2[Amount] ) ),
        [Name] <> BLANK ()
    )
VAR ranked =
    ADDCOLUMNS ( summary, "Rank", RANKX ( summary, [total],, DESC ) )
RETURN
    MAXX ( FILTER ( ranked, [Name] = currName ), [Rank] )

Regards,

Xiaoxin Sheng

[amitchandak]

@tbucki1 , first you should try to address the problem. seem like one of the car name is there in sales not there in car_name. or there sales with balnk car name

create a measure like

calculate(Sum('Sales_Table'[Sales]), filter(care_name, not(isblank(car_name[name]))))

Use this measure in rank and try