topic Re: Filtering on amount >= in DAX Commands and Tips

Filtering on amount >=

Tob_P — Mon, 08 Aug 2022 11:12:14 GMT

I have been working off a piece of DAX that I used previously whereby I only wanted to show sales after a certain date which worked well and wanted to adapt it so that only sales after a certain amount where shown. Here's the DAX...

Product Target Filtered Sales (MSM Amount) =

sumx (

'tblProductTargets',

VAR BonusStartAmount ='tblProductTargets'[Start Amount]

RETURN

CALCULATE(

sum('Nav_Sales History MASTER'[Amount (LCY)]),

KEEPFILTERS(

'Nav_Sales History MASTER'[Amount (LCY)] >= BonusStartAmount)))+0

To test it out, I reset the [start amount] to zero, fully expecting to see the full amount but get this result...

Is there a reason why this would work on dates, but not on amounts? I thought it would be fairly straightforward.

Thanks

Re: Filtering on amount >=

johnt75 — Mon, 08 Aug 2022 13:14:40 GMT

If [Amount (LCY)] is a measure rather than a column I think you will need to use the FILTER function rather than just passing it as a parameter to CALCULATE. Without the FILTER iterator it has no row context in which to evaluate the measure

Re: Filtering on amount >=

Tob_P — Mon, 08 Aug 2022 13:19:25 GMT

Hi johnt75 - Thanks for coming back to me. It's a column, not a measure.

Re: Filtering on amount >=

johnt75 — Mon, 08 Aug 2022 13:32:55 GMT

Then the problem would seem to be in other filters which may be being applied. Is there a relationship from the targets table to sales history?

A good way to see which filters are being applied to a visual is to get the DAX code generated for it from Performance Analyzer and look at that in DAX Studio. Might point you in the right direction

Re: Filtering on amount >=

Tob_P — Mon, 08 Aug 2022 15:24:22 GMT

johnt75Thanks for the suggestion. I'll give that a go and see if anything comes up!

Re: Filtering on amount >=

ChenwuZhu_Gmail — Thu, 11 Aug 2022 06:41:05 GMT

Hi Tob_P ,

Have your questions been resolved and can you share them?

Best regards.

Re: Filtering on amount >=

Tob_P — Thu, 11 Aug 2022 07:28:13 GMT

ChenwuZhu_Gmail

I'm afraid not - still looking for a solution. Nothing flagged up in Performance Analyser and there is a direct relationship between Sales History and Target