How to get dynamic count with date cutoff?

[jseow]

Hi all,

I have attached a sample data set for the problem I'm working on . I have a simple products and reviews table which are joined on product id. 

I am trying to create a view with time(month) on the x-axis that over time shows me in a combo chart:

1. (Line) The percentage of products that existed by the end of the given month that had at least 1 review.

2. (Column)The # of products that had at least 1 review by the end of the given month.

3. (Column)The # of products that had at least 10 reviews by the end of the given month.

I think I did #1 and 2 correctly, by creating 2 new measures and am stuck on # 3, trying to build off of what I did for #2, but the changes I've tried are not producing the desire results:

Products 1+ Reviews =
CALCULATE(
DISTINCTCOUNT(Reviews[Product ID]),

FILTER(ALL(Reviews),Reviews[Review Status] = "Published" && Reviews[Review Date] <=MAX(Reviews[Review Date]))
)

I assume I need a measure that filters to reviews based on the date, then groups by product ID, then filter off anything less than 10 total? Or maybe using the SUMMARIZE function? Any help would be appreciated.

Here is my most recent attempt:

Products 10+ Reviews = 
CALCULATE(
	DISTINCTCOUNT(Reviews[Product ID]),
	FILTER(
		SUMMARIZE (
			Reviews,
			Reviews[Product ID],
			"review_count", 
			CALCULATE(
				COUNTROWS(Reviews),
				FILTER(
					Reviews,
					Reviews[Review Status] = "Displaying" && Reviews[Review Date]<=MAX (Reviews[Review Date])
				)
			)
		),
		review_count >= 10
	)
)

But I get an error "Failed to resolve name 'review_count'. It is not a valid table, cariable, or function name."

I thought I declared the review_count table in the Summarize function, so how do I use it to Filter?

Thanks!

[v-yulgu-msft]

Hi @jseow,

I have checked the .pbix file you uploaded, you should wrap the 'review_count' with square brackets [].

Products 10+ Reviews = 
CALCULATE(
	DISTINCTCOUNT(Reviews[Product ID]),
	FILTER(
		SUMMARIZE (
			Reviews,
			Reviews[Product ID],
			"review_count", 
			CALCULATE(
				COUNTROWS(Reviews),
				FILTER(
					Reviews,
					Reviews[Review Status] = "Displaying" && Reviews[Review Date]<=MAX (Reviews[Review Date])
				)
			)
		),
		[review_count] >= 10
	)
)

Best regards,

Yuliana Gu

[jseow]

@v-yulgu-msft

Thanks for the reply! It looks like using [review_count] allows the measure to evaluate, so the syntax is fixed. Unfortunately, it looks like my logic is off somewhere else, as when I add this measure to my chart, it does not show any columns (presumably it evaluates to 0 for all months?)

Based on the sample data, there should be a product that gets to 10 displayed reviews in May, then a second product in October, so I am not sure what part of my measure is logically incorrect.