Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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 , 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()))
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |