Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
Mar 05, 2025

KPI dax logic not giving correct counts but table visual does

Hi Team,

 

I have below dax for the Doors open kpi:

Doors Open = VAR _count =
    CALCULATE( MAX( BMSDoorStoreJoin[CreatedOn] ), BMSDoorStoreJoin[Status] = "Open" )
VAR _siteid = MAX(BMSDoorStoreJoin[SiteId])
RETURN
    CALCULATE(
        COUNT( BMSDoorStoreJoin[CreatedOn] ),
        BMSDoorStoreJoin[Status] = "Open"&&
        BMSDoorStoreJoin[CreatedOn] = _count &&
        BMSDoorStoreJoin[SiteId] = _siteid 
        
    )

Now, this returns the value of 1 which is wrong according to filtered SiteId(StoreName). 
My expected output from this logic is I want to find unique count of Doors Open(Site[Sitecode]) for the max of createdon date. For example, the data below for Sitecode 472

we have Status as closed and we don't have open. So it should retun nil. but in this case it returns 1. 

My table visual also shows correctly that it has only closed status. When i use my kpi logic in that visual it gives correct count. 

Please advise!

PFA file here BMS Chillers Insights.pbix

 

Thanks in advance!

SergeiBaklan 

admin
BI & Data Analysis
Formulas and Functions
Macros and VBA
Office Scripts
power bi
training
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 05, 2025

    Excellove15 , you calculate _count as max of CreatedOn for OPENED doors.  But on screenshot you filter CreatedOn on max for any status.

    If to use

    VAR _count =
    CALCULATE (
        MAX ( BMSDoorStoreJoin[CreatedOn] )//,
        //BMSDoorStoreJoin[Status] = "Open"
    )

    measure returns nil.

    In addition, in

    'BMSDoorStoreJoin'[SiteId] --> 'Site'[SiteCode]

    you have whole number from one site and text field on another. Better to use the same type.

    • Excellove15's avatar
      Excellove15
      Iron Contributor
      Mar 06, 2025

      Hi SergeiBaklan Sir,

       

      Thanks for your quick response! it means a lot to me🙂

      wow! i didn't realize that sir and _count should be for any status. Now after comment of filter modifier, the calculate works like a gem sir! thanks for quick fix.

      It is my stupidity i didn't check the data model data types. Agree with you sir for use of same type. I will fix it in data flows.

      its working sir 🙂

      many thanks and hope you have a wonderful day!

      i will close this query

Resources