Forum Discussion

jseow's avatar
jseow
Frequent Visitor
8 years ago

How to get dynamic count with date cutoff?

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(m...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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's avatar
jseow
Frequent Visitor
8 years ago

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.