Forum Discussion

Haripoola's avatar
Haripoola
Frequent Visitor
4 years ago
Solved

Count status values based on slicer selected dates duration

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

 

 

COUNT
COUNTROWS
DAX
Filter
Slicer Visualizations
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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()))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Haripoola
      Frequent Visitor
      4 years ago

      Anonymous 

      Thank you for your solution, it worked for me.