Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

FILTER on Measure column

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.
 
  • Anonymous's avatar
    Anonymous
    3 years ago

    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!

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

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