Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ftf
New Member

Using measures in filter expression

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!

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1651565045389.png

 

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

View solution in original post

3 REPLIES 3
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1651565045389.png

 

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

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

amitchandak
Super User
Super User

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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!