KPI dax logic not giving correct counts but table visual does

[sivarajan21]

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

@marcorusso @Ahmedx @danextian @Anonymous @tharunkumarRTK 

[Anonymous · Accepted solution]

Hi, @sivarajan21 

I can't open your file connection.

Maybe you can try this expression:

Doors Open = 
VAR StatusFiltered =
FILTER(
    SUMMARIZE(
        BMSDoorStoreJoin,
        BMSDoorStoreJoin[SiteId],
        "MaxDateOpen", 
        CALCULATE(
            MAX(BMSDoorStoreJoin[CreatedOn]),
            BMSDoorStoreJoin[Status] = "Open"
        )
    ),
    NOT ISBLANK([MaxDateOpen])
)
VAR ActiveSites = 
TOPN(
    1,
    ADDCOLUMNS(
        StatusFiltered,
        "HasValidEntry",
        CALCULATE(
            COUNTROWS(BMSDoorStoreJoin),
            BMSDoorStoreJoin[CreatedOn] = [MaxDateOpen],
            BMSDoorStoreJoin[Status] = "Open"
        ) > 0
    ),
    -1,
    [MaxDateOpen]
)
RETURN
COUNTROWS(FILTER(ActiveSites, [HasValidEntry]))

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sivarajan21]

Hi @Anonymous sir,

thanks for your quick response!

its resolved now