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_Name Test_Value ABC 10 SDF 12 ERT 14 ASD     I have to find the top 1 Test_name based on the Test_value    M...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    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!

     

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