Count status values based on slicer selected dates duration

[Haripoola]

Hi Folks,

I have a fact table which contains Laptop software installation details like below (attached sample copy), 
I'm trying to create a DAX expression that will enable me to return data when a Laptop software installation status is in between a selected date range.

For example consider InstallationJob- 7989 and LaptopID-8973 Data from below table When i select a date range of

12/01/21 to 03/13/22 using a date slicer, It should be treated as failed Laptop and its count should be in Failed count, If i select slicer from 12/01/21 to 03/14/22, its count should be removed from Failed count and should be added in Installed Laptop as it is installed on 03/14/22 (this laptop no longer a failed laptop after installation done successfully but untill previous date it is a failed laptop).
Please let me know if needed more information.

note: I have to count them with combination of InstallationId and LaptopId because same laptop may come for software installation in different Installation Jobs (Like LaptopId-3876).

The output should be like below as per slicer selection on image

Deployed-5

Failed-2 (Installed laptops should be removed)

SWError-1

Installed-3

It would be great if we can atleast count only recent record over InstallationId and LaptopId Combination

[Anonymous · Accepted solution]

Hi @Haripoola ,

In your calculation, you have two keys, InstallationJob and LaptopID. I suggest you to create a calculated column to combine them to one column. This will make calculation easier.

Combine Key Column = COMBINEVALUES("-",'Table'[InstallationJob],'Table'[LaptopID])

Measure:

Count = 
VAR _Installed_Key =
    CALCULATETABLE (
        VALUES ( 'Table'[Combine Key Column] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[InstalledStatus] = "Installed" )
    )
VAR _CountRows =
    CALCULATE ( COUNTROWS ( 'Table' ) )
RETURN
    IF (
        HASONEVALUE ( 'Table'[InstalledStatus] ),
        SWITCH (
            MAX ( 'Table'[InstalledStatus] ),
            "Deployed", _CountRows,
            "Failed",
                CALCULATE (
                    COUNTROWS ( 'Table' ),
                    FILTER ( 'Table', NOT ( 'Table'[Combine Key Column] IN _Installed_Key ) )
                ),
            "Installed", _CountRows,
            "SWError",
                CALCULATE (
                    COUNTROWS ( 'Table' ),
                    FILTER ( 'Table', NOT ( 'Table'[Combine Key Column] IN _Installed_Key ) )
                )
        ),
        DISTINCTCOUNT ( 'Table'[Combine Key Column] )
    )

Result is as below.

12/01/21 to 03/14/22:

12/01/21 to 03/13/22

Best Regards,
Rico Zhou

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

[Haripoola]

@Anonymous 

Thank you for your solution, it worked for me.

[amitchandak]

@Haripoola , Try a measure like

measure =
var _max = maxx(filter(Table, Table[InstallationId] = max(Table[InstallationId]) && [LaptopId] = max(Table[LaptopId])), Table[StatusUpdateOn])
var _status= maxx(filter(Table, Table[InstallationId] = max(Table[InstallationId]) && [LaptopId] = max(Table[LaptopId]) && Table[StatusUpdateOn]&& Table[StatusUpdateOn] =_max), [InstalledStatus])
return
countx(summarize(Table, Table[InstallationId] ,[LaptopId]), if(Max(Table[InstalledStatus]) = _status, [InstallationId], blank()))