Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Haripoola
Frequent Visitor

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

 

Haripoola_0-1647279337107.png

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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:

1.png

12/01/21 to 03/13/22

2.png

 

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.

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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:

1.png

12/01/21 to 03/13/22

2.png

 

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.

@v-rzhou-msft 

Thank you for your solution, it worked for me.

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors