topic Re: Dynamic Filtering in DAX Commands and Tips

Dynamic Filtering

Anonymous — Fri, 24 Jun 2022 16:52:30 GMT

Hello All,

I've got a report with cumulative data on a chart along with a card that displays the total obligations.

The only way to get the card to display the correct amount is to do a relative filter to show items in the current month. Without the filter, the card will display the sum of all cumulative monthly totals (~$5b) which is incorrect.

If I select a month on the chart, the card does not update since it already has a filter for items in the current month.

I'm looking for a solution that allows the "Total Obligations" card to default to filtering for the current month until a month is selected on the chart. Then the filter would change to selected month. I don't know if this can be done, but I'm open to any suggestions.

Re: Dynamic Filtering

Anonymous — Tue, 28 Jun 2022 02:07:17 GMT

Hi Anonymous ,

Please try this measure.

Total Obligations = CALCULATE ( SUM ( 'Table'[Obligations] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[MonthNum] = SELECTEDVALUE ( 'Calendar'[MonthNum], MONTH ( TODAY () ) ) ) )

By default the current month is filtered until a month is selected on the graph.

Attached PBIX file for reference.

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

Re: Dynamic Filtering

Anonymous — Tue, 28 Jun 2022 15:35:47 GMT

Gao, you're a beast. Thanks for the help!