Forum Discussion

Ethanhunt123's avatar
Ethanhunt123
Icon for Helper IV rankHelper IV
6 years ago
Solved

Top N for only specific category (Avoid blanks)

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

Test_NameTest_Value
ABC10
SDF12
ERT14
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
Need Help

5 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Ethanhunt123
      Icon for Helper IV rankHelper IV
      6 years ago

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

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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!

     

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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]))