Incorrect data using var, but correct when display as a table

[PBI5851]

Hello,

 I am trying to retrieve a count from table where the filter is on the date Slicer. So the active count is the count(*) from the Product table for that specific date. 

DateValue = CALCULATE(MAX(Datetable[Date]),ALLSELECTED(Datetable[Date]))

ActiveCount =

var __MaxValue = [DateValue]

Return

CALCULATE(COUNT(Product[ID]), Product[Shipdate]= __MaxValue )

When i display the above measure as  a card, i get an incorrect number. I know it is incorrect, because (1) when i run the query against the DB in sql , i get the correct number and (2) when i display the Product in a table in Power BI, and filter based on the specific date, the count (in total) is exactly matching the DB. Not sure what i'm missing. I do not have duplicate in the ID on the product table. 

Please help. 

[Icey]

Hi @PBI5851 ,

Is this problem solved?

Best Regards,

Icey

[PBI5851]

Almost. using the "ALL " in the calculate syntax works. But unfortunately, it seems to not being affected by the Category slicer. 

So when i change the date values using the slicer the count i get for min and max is correct. But when i change the filter on the Category slicer which controls the page, the min and max dont change. They remain the same as mentioned above. 

[az38]

Hi @PBI5851 

if you return __MaxValue what do you see in card visual?

ActiveCount =

var __MaxValue = [DateValue]
Return
__MaxValue 

It might be affected by ALLSELECTED()

[PBI5851]

@az38 When returning the __Maxvalue, i get the correct date i.e the last date as per the date slicer. 

[az38]

@PBI5851 

try to use ALL() or ALLSELECTED() also

ActiveCount =

var __MaxValue = [DateValue]
Return
CALCULATE(COUNT(Product[ID]), Product[Shipdate]= __MaxValue, ALL(Product) )

or

ActiveCount =

var __MaxValue = [DateValue]
Return
CALCULATE(COUNT(Product[ID]), Product[Shipdate]= __MaxValue, ALLSELECTED(Product[Shipdate]) )​