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,

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

    PatrickSeidl

     

    Please could you post some sample data and expected results

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