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

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]
)
  • PatrickSeidl's avatar
    PatrickSeidl
    Frequent Visitor
    8 years ago

    Hi,

    this is awesome, thank you so much.

     

    All the best,

    Patrick

  • 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