topic Re: FILTER on Measure column in DAX Commands and Tips

FILTER on Measure column

Anonymous — Mon, 08 May 2023 10:50:35 GMT

Hello everyone,

I am trying to find a solution for following use case:

I have a table where the difference between two values with time-intelligence is calculated by a measure:

First measure

PrevMonthValue= CALCULATE(SUM(Table[Price]), DATEADD(Table[Date],-1, MONTH))

Second measure

DeltaPrice = [Price]-[PrevMonthValue]

Now I would like to filter only those values of my second measure which are <> 0.

I have tried it by using FILTER expression or also with a check upon a boolean value but it does not work.

Follwing thing I hava tried:

Changed =

VAR vChangedTable =

ADDCOLUMNS (

Table,

"@Delta", [DeltaPrice]

)

VAR vFilteredTable =

FILTER (

vChangedTable,

[@Delta] <> 0

)

RETURN

CALCULATE (

SUM(Table[Price]) ,

vFilteredTable

)

But it does not work.

Is there any other elegant way to filter upon a measure?

Thanks in advance for your support.

Re: FILTER on Measure column

tamerj1 — Mon, 08 May 2023 11:32:04 GMT

Hi Anonymous
What do you have in the table visual? Can place a screenshot?

Re: FILTER on Measure column

Anonymous — Mon, 08 May 2023 11:36:33 GMT

Hi tamerj1

this is the visual:

Re: FILTER on Measure column

tamerj1 — Mon, 08 May 2023 11:44:48 GMT

Anonymous
Place the following measure in the filter pane of the matrix, select is not balnk and apply the filter.

Changed = COUNTROWS ( FILTER ( VALUES ( 'Table'[Article] ), CALCULATE ( [DeltaPrice], ALL ( [Date] ) ) <> 0 ) )

Re: FILTER on Measure column

Anonymous — Mon, 08 May 2023 11:57:00 GMT

Hi tamerj1

thanks, but does not work, because the value of "Changed" is always 1.

I have also tried this with an if statement by checking the difference between the two values, but the filter did not work. Any other idea?

Re: FILTER on Measure column

tamerj1 — Mon, 08 May 2023 12:00:14 GMT

Anonymous
Would you please paste a screenshot of your measure code?

Re: FILTER on Measure column

Anonymous — Mon, 08 May 2023 12:11:18 GMT

tamerj1

Changed =

COUNTROWS (

FILTER (

VALUES ( 'Table'[Article] ),

CALCULATE ( [Delta], ALL ( Table[Date] ) ) <> 0

)

Re: FILTER on Measure column

Anonymous — Mon, 08 May 2023 14:09:43 GMT

tamerj1

Found a possible solution by changing your measure into this:

Changed =

COUNTROWS (

FILTER (

ALL ( 'Table'[Price] ), [DeltaPrice] <> 0

)

It works by returning 1 and afterwards when set in the visual as a filter.

Thanks for your support!