Dax logic for KPI not giving correct count but table visual dax gives correct count

[sivarajan21]

Hi Team,

I have a dax measure created for this KPI card.

Dax for KPI card :

Breach Total = 
CALCULATE(COUNTROWS(
    FILTER(
        Breachopenmin,
        VAR _createdon = CALCULATE(
        MAX(Breachopenmin[CreatedOn]),
        ALL(Breachopenmin))
        VAR _devid = Breachopenmin[DeviceId]
        VAR _status = Breachopenmin[DoorStatus]
        VAR _Sstatus = Breachopenmin[StoreStatus]
        VAR _openBMinutes = Breachopenmin[OpenBreachMinutes]
        RETURN
        (_Sstatus = "C" && _openBMinutes > 0) ||
        (_openBMinutes > 0 && _status = "Closed") ||
        (_openBMinutes > 0 && max(Breachopenmin[CreatedOn]) = _createdon && Breachopenmin[DeviceId] = _devid)
    )
))

But its giving wrong count here 19 instead of 1.

Reason I am saying one here is look at the table visual and its dax to count the breach and its working correctly.

Dax for table visual:

Breach = 
VAR _createdon = 
    CALCULATE(
        MAX(Breachopenmin[CreatedOn]),
        ALLEXCEPT(Breachopenmin, Breachopenmin[DeviceId])
    )
VAR _devid = SELECTEDVALUE(Breachopenmin[DeviceId])
VAR _status = SELECTEDVALUE(Breachopenmin[DoorStatus])
VAR _Sstatus = SELECTEDVALUE(Breachopenmin[StoreStatus])
VAR _openBMinutes = SELECTEDVALUE(Breachopenmin[OpenBreachMinutes])

RETURN
IF(
    (_Sstatus = "C" && _openBMinutes > 0) ||
    (_openBMinutes > 0 && _status="Closed") ||
     (_openBMinutes > 0 && MAX(Breachopenmin[CreatedOn]) = _createdon && _devid = _devid),
    1,
    BLANK()
)

My expected count here is 1. I don't think the KPI card visual needs to be this complex as we already have table visual breach working and just few things that needs change but am missing out.

KPI logic should count only the one instance that the max row and not all rows in table.
PFA file here BMS Door Insights.pbix

Please could you help to resolve kpi card count whenever you are free

Please let me know if you have any issues.

Many thanks in advance!

@tharunkumarRTK @marcorusso @Anonymous @Greg_Deckler @ray_aramburo 

[danextian · Accepted solution]

Hi @sivarajan21 

Breach, Warnings and _SiteName measures are evaluated for each row in your table viz but no such evaluation happens when you use the KPI viz. SELECTEDVALUE also returns blank by default if not  a single value is selected. If you want those measures evaluated virtually, you can create a virtual table instead inside a measure and filter it.

Breach3 = 
SUMX (
    FILTER (
        KEEPFILTERS (
            SUMMARIZECOLUMNS (
                'Door'[SiteId],
                'Door'[CreatedOn],
                'Door'[Status],
                'OpenstateDuration'[OpenMinutes],
                'Store Status'[Status],
                "@SiteName", [_SiteName],
                "@Warnings", [Warnings],
                "@Breach", [Breach]
            )
        ),
        AND ( CONTAINSSTRING ( [@Warnings], "Yes" ), NOT ( ISBLANK ( [@SiteName] ) ) )
    ),
    [@Breach]
)

[sivarajan21]

Hi @danextian ,

Thanks for your quick response!

It made my day and it means a lot. Your service to community is invaluable. 

Keep up the good work! please let me know if you need my help. For example, i am on linkedin, if you want to endore your blogs/work etc. 😊

You saved me from hours of frustration!

i will close this query