Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

topn dax for negative values

hi, i have no problem getting result for topn dax for positive values. however, i need help to resolve the topn dax for negative values. this is my typical topn dax: TopN.ADPR = CALCULATE ( [1...
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous 

    Sort Ascending should give you top negative numbers. Or Top means all near to 0. Then have a filter of <0 in your measure

     

    order
    (Optional) A value that specifies how to sort orderBy_expression values, ascending or descending:

    value alternate value Description
    0 (zero) FALSE Sorts in descending order of values of order_by.

    This is the default value when order parameter is omitted.
    1 TRUE Ranks in ascending order of order_by.

     

    Or use rank

    For Rank Refer these links
    https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
    https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
    https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

     

     
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    You may create a what-if parameter as below.

     

    Then you may create a measure as below.

    IsDisplay = 
var _topn = SELECTEDVALUE(Parameter[Parameter])
var _name = SELECTEDVALUE('Table'[Name])
var tab = 
ADDCOLUMNS(
    ALLSELECTED('Table'),
    "Rank",
    RANKX(
        ALLSELECTED('Table'),
        'Table'[Value]
    )
)
var _rank = 
SUMX(
    FILTER(
        tab,
        [Name] = _name
    ),
    [Rank]
)
return
IF(
    _rank<=_topn,
    1,0
)

     

    Finally you need to put the measure in the visual level filter of a table visual. Here is the result.

     

    Best Regards

    Allan

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous 

Sort Ascending should give you top negative numbers. Or Top means all near to 0. Then have a filter of <0 in your measure

 

order
(Optional) A value that specifies how to sort orderBy_expression values, ascending or descending:

value alternate value Description
0 (zero) FALSE Sorts in descending order of values of order_by.

This is the default value when order parameter is omitted.
1 TRUE Ranks in ascending order of order_by.

 

Or use rank

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

 

 
Anonymous's avatar
Anonymous
Not applicable
6 years ago

thanks for your time & efforts in trying to help, amitchandak.

if top means all near to 0, please advise on how to have a filter of <0 in my measure in same dax that wrote earlier?

krgds, -nik

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

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create a What-if parameter table as below.

     

    mn-value = GENERATESERIES(0, 6, 1)

     

     

    Then you need to create a measure like below.

     

    Result = 
var _topn = SELECTEDVALUE('mn-value'[mn-value])
return
CALCULATE(
    SUM('Table'[Value]),
    TOPN(
        _topn,
        FILTER(
            ALL('Table'),
            'Table'[Value]<0
        ),
        'Table'[Value]
    )
)

     

     

    Result:

     



    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      thanks, v-alq-msft.

       

      i'm sorry for not responding earlier (took a short break). also, i'm sorry for not making it clearer. 

       

      the situation is if i have all negative values, how do i do top-n ranking for them?

      Using the table you reproduced, the list will be as follows:

      NameValue
      Amy-11
      Allen-20

      Mike

      		-19
      Micheal-46
      Lucy-16
      Lily-33
      Jack-25
      John-10

       

      if i filter for top-3, it'll be-

      NameValue
      John-10
      Amy-11
      Lucy-16

       

      how will the dax b to rank the negative values as in the case above, please?

      tks & kind rgds, -nik

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

        Hi, Anonymous 

         

        You may create a what-if parameter as below.

         

        Then you may create a measure as below.

        IsDisplay = 
var _topn = SELECTEDVALUE(Parameter[Parameter])
var _name = SELECTEDVALUE('Table'[Name])
var tab = 
ADDCOLUMNS(
    ALLSELECTED('Table'),
    "Rank",
    RANKX(
        ALLSELECTED('Table'),
        'Table'[Value]
    )
)
var _rank = 
SUMX(
    FILTER(
        tab,
        [Name] = _name
    ),
    [Rank]
)
return
IF(
    _rank<=_topn,
    1,0
)

         

        Finally you need to put the measure in the visual level filter of a table visual. Here is the result.

         

        Best Regards

        Allan