Forum Discussion

Deermeat's avatar
Deermeat
Icon for Resolver I rankResolver I
4 years ago
Solved

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.

 

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!

  • 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!

2 Replies

  • 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!