Forum Discussion

PBI5851's avatar
PBI5851
Helper V
6 years ago

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

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. 
Need Help

5 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      PBI5851
      Helper V
      6 years ago

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

      • az38's avatar
        az38
        Community Champion
        6 years ago

        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]) )​
    • PBI5851's avatar
      PBI5851
      Helper V
      6 years ago

      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.