Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello I have a table like the one below where information about servers is captured at various times. The table only shows one company, but in reality there are several:
partner | forest | Role | ScanDate | name | operatingsystem | operatingsystemversion | CC_OSFamily |
Contoso | Contoso.com | Production | 6/29/2018 10:43:02 AM | Svr01 | Windows Server 2008 R2 Standard | 6.1 (7601) | Windows Server 2008 R2 |
Contoso | Contoso.com | Production | 6/29/2018 10:43:02 AM | Svr02 | Windows Server 2008 R2 Standard | 6.1 (7601) | Windows Server 2008 R2 |
Contoso | Contoso.com | Production | 6/29/2018 10:43:02 AM | Svr03 | Windows Server 2008 R2 Standard | 6.1 (7601) | Windows Server 2008 R2 |
Contoso | Contoso.com | Production | 11/15/2018 10:43:02 AM | Svr01 | Windows Server 2008 R2 Standard | 6.1 (7601) | Windows Server 2008 R2 |
Contoso | Contoso.com | Production | 11/15/2018 10:43:02 AM | Svr02 | Windows Server 2012 R2 Standard | 6.3 (9600) | Windows Server 2012 R2 |
Contoso | Contoso.com | Production | 11/15/2018 10:43:02 AM | Svr03 | Windows Server 2016 Standard | 10.0 (14393) | Windows Server 2016 |
The last column is calculated:
#"Added Conditional Column" = Table.AddColumn(#"Remove_Stale _&_Disabled_Servers", "CC_OSFamily", each if Text.StartsWith([operatingsystemversion], "5.0") then "Windows 2000 Server" else if Text.StartsWith([operatingsystemversion], "5.2") then "Windows Server 2003" else if Text.StartsWith([operatingsystemversion], "6.0") then "Windows Server 2008" else if Text.StartsWith([operatingsystemversion], "6.1") then "Windows Server 2008 R2" else if Text.StartsWith([operatingsystemversion], "6.2") then "Windows Server 2012" else if Text.StartsWith([operatingsystemversion], "6.3") then "Windows Server 2012 R2" else if Text.StartsWith([operatingsystemversion], "10.0") then "Windows Server 2016" else null),
I then created custom measures for each OS:
CM_OSFamily_2008R2 = CALCULATE (DISTINCTCOUNT (ServerReports[dnshostname]),ServerReports[CC_OSFamily]="Windows Server 2008 R2")
This allowed me to create a graph such as this:
Now I would like to also show % change in the number of servers by OS, but can't figure out how.
The one issue with this is that the scan dates are not the same for the different environments, or at regular intervals.
I was thinking more along the lines of this:
For each OS, No. of servers[earliest scan date] / No. of servers [latest scan date] * 100
I came as far as figuring out how to find the earliest and latest scan dates:
CM_First_Sample = CALCULATE(MIN(ServerReports[ScanDate]),VALUES(ServerReports[forest])) CM_Last_Sample = CALCULATE(MAX(ServerReports[ScanDate]),VALUES(ServerReports[forest]))
But can't get past this.
Any help is greatly appreciated!
Hi @PowerBeeEye,
Please try below measures. Also, you need to place column [CC_OSFamily] into chart legend.
CM_First_Sample = CALCULATE ( MIN ( ServerReports[ScanDate] ), VALUES ( ServerReports[forest] ), ALLEXCEPT ( ServerReports, ServerReports[CC_OSFamily] ) ) CM_Last_Sample = CALCULATE ( MAX ( ServerReports[ScanDate] ), VALUES ( ServerReports[forest] ), ALLEXCEPT ( ServerReports, ServerReports[CC_OSFamily] ) ) CM_OSFamily earliest = CALCULATE ( DISTINCTCOUNT ( ServerReports[name] ), FILTER ( ALLEXCEPT ( ServerReports, ServerReports[forest], ServerReports[CC_OSFamily] ), ServerReports[ScanDate] = [CM_First_Sample] ) ) CM_OSFamily latest = CALCULATE ( DISTINCTCOUNT ( ServerReports[name] ), FILTER ( ALLEXCEPT ( ServerReports, ServerReports[CC_OSFamily], ServerReports[forest] ), ServerReports[ScanDate] = ServerReports[CM_Last_Sample] ) ) % change = [CM_OSFamily earliest]/[CM_OSFamily latest]
Best regards,
Yuliana Gu
Thank you, but I don't think its working; I have a few follow up questions:
1. What is the best visializer for this? I used Area chart, but is another better?
2. I can't add CC_OSFamily to the chart legend - no error, it just does not add.
3. What measures should I add to the Values?
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |