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

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