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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors