Top N for only specific category (Avoid blanks)

[Ethanhunt123]

I have a table that has 2 columns, Test_Name , Test_Value

Test_Name	Test_Value
ABC	10
SDF	12
ERT	14
ASD

I have to find the top 1 Test_name based on the Test_value 

My DAX looks like this

Test =

CALCULATE (

( Test_Name),

TOPN ( 1, ALL(Test_Name) , Test_value DSC),

VALUES ( test_Name )

)

The output I am getting is ASD( which is not correct, it should not consider blank values) The output should be ERT

[negi007]

@Ethanhunt123  You can create below simple measure to obtain the value against TOP value.

Top_Val = LOOKUPVALUE('Top N'[Test_Name],'Top N'[Test_Value],Max('Top N'[Test_Value]))

[v-kelly-msft · Accepted solution]

Hi  @Ethanhunt123 ,

Create a measure as below:

Measure = 
CALCULATE(MAX('Table'[Test_Name]),TOPN(1,FILTER(ALL('Table'),'Table'[Test_Value]<>BLANK()),CALCULATE(MAX('Table'[Test_Value])),DESC))

And you will see:

For the related .pbix file,pls see attached.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[PaulDBrown]

@Ethanhunt123 

Use RANKX instead of TOPN

(The name of the table in my exampe is 'TopN')

Rank Value = RANKX(ALL('TopN'), [Sum of Value],,DESC)

and if you want a measure for the top name only:

Top Name for Card Visual = CALCULATE(MAX('TopN'[Test_Name]), FILTER('TopN', [Rank Value] = 1))

[amitchandak]

@Ethanhunt123 , Try like measures like

measure = calculate(sum(Test_Value), filter(Table,not(isblank(Table[Test_Value]))))

CALCULATE (
[measure],
TOPN ( 1, ALL(Test_Name) , [measure] DSC),
VALUES ( test_Name )
)

[Ethanhunt123]

The output would be Test_name, This is still giving me TEST_Name with the blank value