Using measures in filter expression

[ftf]

Hello Community,

I'm new to PowerBi and currently wondering about how the filter in CALCULATE works.

I have a column "ColumnA" which is part of "Table1" that should be filtered and returned. Therefore, I calculate a new measure with the following expression:

newmeasure = CALCULATE(Table1[ColumnA], 'Table1'[Category] IN { "A" }, 'Table1'[Quarter] IN { 202202 })

This works fine and I'm happy with the result. However, as the quarter changes with time, I do not want to provide a fixed value but use another measure for filtering. And so I replaced the above expression with:

newmeasure = CALCULATE(Table1[ColumnA], 'Table1'[Category] IN { "A" }, 'Table1'[Quarter] IN {VALUE('Table2'[cur_quarter]) })

The measure "cur_quarter" is part of "Table2" and contains the current quarter as string. The new expression fails with the error message "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression.". From what I understand is that CALCULATE does not support measures or columns in the filter section, but to me it seems like an easy task and I see no reason why it does not work (it would work in any programming language). So far I did not find a realization that works, also trying other functions.

How can this be implemented? Do you have an idea?

I'm happy to hear your suggestions. Thank you!

[v-cazheng-msft · Accepted solution]

Hi @ftf,

Just the error said, you have used a TRUE/FALSE expression as a table filter expression. This is not allowed. You could refer to this official doc to learn what kind of filters can be used as the parameters of CALCULATE function: CALCULATE function (DAX) - DAX | Microsoft Docs

In addition, you need make some changes to your Measure formula.

CalculationOnFilters =
CALCULATE (
    SUM ( Table1[ColumnA] ),
    FILTER (
        Table1,
        'Table1'[Category]
            IN { "A" }
                && 'Table1'[Quarter] IN { VALUE ( 'Table2'[current_quarter] ) }
    )
)
//CALCULATE(SUM(Table1[ColumnA]),'Table1'[Category] IN { "A" },'Table1'[Quarter] IN {VALUE('Table2'[current_quarter]) })

With the sample data created, the result looks like this.

Also, attach the pbix file as reference. Hope it helps.

If this 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 me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[ftf]

Thanks for your help, @v-cazheng-msft!

[amitchandak]

@ftf , QTD/Qtr based on selected date or today

QTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))