Use value if entry is missing compared to another table

[PatrickSeidl]

Hi all,

I have a table with SLA information. This table, however, only contains objects with violations per every hour, not those without. The table basically looks like that:

StateChanges:

ServerName;DateTime;HealthyPercent;CriticalPercent

serverA;2018-02-18 0:00;95;5

serverB;2018-02-18 0:00;90;10

serverC;2018-02-18 0:00;0;100

serverA;2018-02-18 1:00;90;10

serverC;2018-02-18 1:00;0;100

...

As you see, serverB had a violation at 0:00 but is missing at 1:00 because it was available at 100%. serverD is missing at all.

Further, I have another table where all servers are listed:

GroupTable:

ServerName;

serverA;

serverB;

serverC;

serverD;

...

What I am searching for is a way to show serverB with 100% healthy at every time where there was no validation. Since the time is a sliding window (last 3 months) it cannot be a fixed table and needs to be something calculated "on the fly".

Those computers without any entry in the first table should show 100% healthy.

At the end I am expecting data like (with the bold ones calculated):

ServerName;DateTime;HealthyPercent;CriticalPercent

serverA;2018-02-18 0:00;95;5

serverB;2018-02-18 0:00;90;10

serverC;2018-02-18 0:00;0;100

serverD;2018-02-18 0:00;100;0

serverA;2018-02-18 1:00;90;10

serverB;2018-02-18 1:00;100;0

serverC;2018-02-18 1:00;0;100

serverD;2018-02-18 1:00;100;0

...

Any ideas?

Thanks for your time in advance,

Patrick

PS: My previous post has been marked as spam, no clue why. So, sorry for posting again.

[Zubair_Muhammad · Accepted solution]

@PatrickSeidl

Please see attached file with your sample data

[Zubair_Muhammad]

@PatrickSeidl

You can use this Calculated Table...I believe

From the Modelling Tab>>NEw Table

New Table =
ADDCOLUMNS (
    ADDCOLUMNS (
        CROSSJOIN ( ALL ( GroupTable[ServerName] ), ALL ( StateChanges[DateTime] ) ),
        "Healthy Percent",
        VAR mycalc =
            CALCULATE (
                SUM ( StateChanges[HealthyPercent] ),
                FILTER (
                    StateChanges,
                    StateChanges[ServerName] = EARLIER ( [ServerName] )
                        && StateChanges[DateTime] = EARLIER ( [DateTime] )
                )
            )
        RETURN
            IF ( ISBLANK ( mycalc ), 100, mycalc )
    ),
    "Critical Percent", 100 - [Healthy Percent]
)

[Zubair_Muhammad · Accepted solution]

@PatrickSeidl

Please see attached file with your sample data

[PatrickSeidl]

Hi @Zubair_Muhammad,

now it's getting a bit more complicated...

In the StateChanges I have "Warning Percent" as well (I did not mention yet for keeping it simple but probably that wasn't a good idea). So, simply calc 100 - "Healthy Percent" does not help and my other calculations (SUM, AVG) take the same value for all rows. No idea how to move on from here.

Probably you could help again?

Thanks again and all the best from Austria,

Patrick

[Zubair_Muhammad]

@PatrickSeidl

Please could you post some sample data and expected results

[PatrickSeidl]

Hi @Zubair_Muhammad

Sure, here you go...

StateChanges:

ServerName;DateTime;HealthyPercent;WarningPercent;CriticalPercent

serverA;2018-02-18 0:00;95;0;5

serverB;2018-02-18 0:00;90;5;5

serverC;2018-02-18 0:00;0;0;100

serverA;2018-02-18 1:00;85;5;10

serverC;2018-02-18 1:00;0;0;100

...

GroupTable:

ServerName;

serverA;

serverB;

serverC;

serverD;

...

Result:

ServerName;DateTime;HealthyPercent;WarningPercent;CriticalPercent

serverA;2018-02-18 0:00;95;0;5

serverB;2018-02-18 0:00;90;5;5

serverC;2018-02-18 0:00;0;0;100

serverD;2018-02-18 0:00;100;0;0

serverA;2018-02-18 1:00;85;5;10

serverB;2018-02-18 1:00;100;0;0

serverC;2018-02-18 1:00;0;0;100

serverD;2018-02-18 1:00;100;0

...

Guess that is what it is supposed to look.

Thanks again,

Patrick

[Zubair_Muhammad]

Hi @PatrickSeidl

My apologies for late reply

There is too much work these days in my office

Hopefully this will work

New Table =
ADDCOLUMNS (
    ADDCOLUMNS (
        CROSSJOIN ( ALL ( GroupTable[ServerName] ), ALL ( StateChanges[DateTime] ) ),
        "Healthy Percent",
        VAR mycalc =
            CALCULATE (
                SUM ( StateChanges[HealthyPercent] ),
                FILTER (
                    StateChanges,
                    StateChanges[ServerName] = EARLIER ( [ServerName] )
                        && StateChanges[DateTime] = EARLIER ( [DateTime] )
                )
            )
        RETURN
            IF ( ISBLANK ( mycalc ), 100, mycalc )
    ),
    "Critical Percent",
    VAR result =
        LOOKUPVALUE (
            StateChanges[CriticalPercent],
            StateChanges[ServerName], [ServerName],
            StateChanges[DateTime], [DateTime]
        )
    RETURN
        IF ( ISBLANK ( result ), 0, result ),
    "Warning Percent",
    VAR result =
        LOOKUPVALUE (
            StateChanges[WarningPercent],
            StateChanges[ServerName], [ServerName],
            StateChanges[DateTime], [DateTime]
        )
    RETURN
        IF ( ISBLANK ( result ), 0, result )
)

[Zubair_Muhammad · Accepted solution]

@PatrickSeidl

File attached here as well

[PatrickSeidl]

This is amazing, thank you so much!

[PatrickSeidl]

Hi,

this is awesome, thank you so much.

All the best,

Patrick