Forum Discussion

10168's avatar
10168
Icon for Helper I rankHelper I
3 years ago
Solved

Calculated column that excludes topN value from a column within the same table

Dax Experts, I have a data model with calender (picture attached), I have the report with 'Fiscal Month Key' on X-axis, however I'm trying to use a new calculated column as X-axis on my report but I need to exclude TOPN value from the column, here (202208- FiscalMonthKey), I tried MAXX function, however I havent been successful, any help or direction is really appreciated. 

 

Eventually I'm trying to switch between these two axises (one that includes MAX month and one without). 

 

Thanks so much in advance 

 

 

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi 10168 ,

    You can create a measure like:

    Measure = 
var _max = CALCULATE(MAX('Calendar'[Fiscal Month Key]),ALL('Calendar'))
var _result = IF(MAX('Calendar'[Fiscal Month Key])=_max,1)
return
_result

     If you need to keep the external filter, you can replace ALL with ALLSELECTED.

     

    Then set the visual level filter and bookmark:

    With the bookmark button, you can freely switch back and forth.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    10168 , A new flag that can help

    if([Fiscal Month key] =max([Fiscal Month key]), "Exclude", "Include")

     

     

    or a meausre 

     

    var _max = maxx(allselected(Date), date[Fiscal Month key])

    return

    calculate(sum(Table[value), filter(date, date[Fiscal Month key] <_max))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi 10168 ,

    You can create a measure like:

    Measure = 
var _max = CALCULATE(MAX('Calendar'[Fiscal Month Key]),ALL('Calendar'))
var _result = IF(MAX('Calendar'[Fiscal Month Key])=_max,1)
return
_result

     If you need to keep the external filter, you can replace ALL with ALLSELECTED.

     

    Then set the visual level filter and bookmark:

    With the bookmark button, you can freely switch back and forth.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data