cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Deermeat
Resolver I
Resolver I

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter exp

Hello,

I'm trying to find averages for data that is 3 months ago from the current date chosen in the slicer. I have created this measure to solve this issue: I'm only matching Month and Year, as each month will only have 1 date. 

 

Score average = 
CALCULATE(
    AVERAGE( Sheet1[Score] ), 
        ALL( Sheet1[Score] ), 
            YEAR(Sheet1[Date]) = YEAR([3monthsago]),
            MONTH(Sheet1[Date]) = MONTH([3monthsago])
)

 

And [3monthsago] is: Can safely ignore the -10, it is just to ensure that the right month is chosen.

 

3monthsago = DATE(YEAR([CurrentDate]), MONTH([CurrentDate]) - 3,DAY([CurrentDate] -10))

 

And [CurrentDate] is just the quick measure of concatenated list of values of the Date used in the slicer, so that I can identify which Date is being chosen in the slicer:

 

CurrentDate = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Sheet1'[Date])
VAR __MAX_VALUES_TO_SHOW = 999
RETURN
	IF(
		__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
		CONCATENATE(
			CONCATENATEX(
				TOPN(
					__MAX_VALUES_TO_SHOW,
					VALUES('Sheet1'[Date]),
					'Sheet1'[Date],
					ASC
				),
				'Sheet1'[Date],
				", ",
				'Sheet1'[Date],
				ASC
			),
			", etc."
		),
		CONCATENATEX(
			VALUES('Sheet1'[Date]),
			'Sheet1'[Date],
			", ",
			'Sheet1'[Date],
			ASC
		)
	)

 

 

However, when I try to use Score average, I get the error: A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Deermeat_0-1651277260679.png

 

This error seems to be because of the fact that [3monthsago] is a measure. Is there anyway to fix this?

Sample File with Error: https://drive.google.com/file/d/1LFAZpJ_Z-pLKwJjRqo2VkC_sIgOXQLos/view?usp=sharing

Thank you for your help!

1 ACCEPTED SOLUTION
karnold
Resolver I
Resolver I

You can use a VAR in place of the measure:

 

Score average = 
VAR _3MonYear = YEAR([3monthsago]
VAR _3MonMonth = MONTH([3monthsago]
RETURN
CALCULATE(
    AVERAGE( Sheet1[Score] ), 
        ALL( Sheet1[Score] ), 
            YEAR(Sheet1[Date]) = _3MonYear,
            MONTH(Sheet1[Date]) = _3MonMonth
)

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!

View solution in original post

2 REPLIES 2
karnold
Resolver I
Resolver I

You can use a VAR in place of the measure:

 

Score average = 
VAR _3MonYear = YEAR([3monthsago]
VAR _3MonMonth = MONTH([3monthsago]
RETURN
CALCULATE(
    AVERAGE( Sheet1[Score] ), 
        ALL( Sheet1[Score] ), 
            YEAR(Sheet1[Date]) = _3MonYear,
            MONTH(Sheet1[Date]) = _3MonMonth
)

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!

Thank you! It work perfectly.

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors