Forum Discussion

PatrickSeidl's avatar
PatrickSeidl
Frequent Visitor
8 years ago
Solved

Use value if entry is missing compared to another table

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: Ser...
Need Help
PatrickSeidl's avatar
PatrickSeidl
Frequent Visitor
8 years ago

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's avatar
Zubair_Muhammad
Community Champion
8 years ago

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