How to create a Summary Box

[darrennhg]

Hi guys I'm trying to create a summary box above my table  that changes colour or shows a tick or cross depending on whether everything listed in my table for that day is working fine. For example: If all the servers on Thursday switched on Successfully then we'll a green box or a tick and if even one server fails on Saturday then thats a red box or cross. I've been trying to use the card visual to create whats in my head, but I can't seem to figure it out.

Similar to this.

Apologies if I haven't explained this well.

Thanks in advance!

[v-xiaotang]

Hi @darrennhg 

I forgot to say, you have to unpivot the data first, then remove "()" in date column,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstUjA0MlbSUQouTU5OLS4mi6WrFKsDN87IgJqmmVniUBWeWJSXmZdOomlmQBG3xMyc1BQSjMBhmCVqsMGNhTOIMzU2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Backup Server Name" = _t, #" (30/12/2021)" = _t, #" (31/12/2021)" = _t, #" (01/01/2022)" = _t, #" (02/01/2022)" = _t, #" (03/01/2022)" = _t, #" (04/01/2022)" = _t, #" (05/01/2022)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Backup Server Name", type text}, {" (30/12/2021)", type text}, {" (31/12/2021)", type text}, {" (01/01/2022)", type text}, {" (02/01/2022)", type text}, {" (03/01/2022)", type text}, {" (04/01/2022)", type text}, {" (05/01/2022)", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Backup Server Name"}, "Attribute", "Value"),
    #"Split Column by Positions" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByPositions({0, 2, 12}), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.1", "Attribute.3"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns", {{"Attribute.2", type date}}, "en-HK"),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"Attribute.2", "Date"}})
in
    #"Renamed Columns"

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[v-xiaotang]

Hi @darrennhg 

please provide more details, so that we can work on further,

(1) a sample file, you can replace raw data with bogus data to protect your privacy.

(2) or provide some sample data that fully covers your issue/question

(3) give your expected result based on the sample you provide

Kindly note: Please ensure the data in sample is concise and representative.

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[darrennhg]

Backup Server Name	Thursday  (30/12/2021)	Friday  (31/12/2021)	Saturday  (01/01/2022)	Sunday  (02/01/2022)	Monday  (03/01/2022)	Tuesday  (04/01/2022)	Wednesday  (05/01/2022)
Server 123	Success	Success	Success	Success	Success	Success	-
Server 20	Success	Success	Success	Success	Success	Success	-
Server 69	Success	Success	Warning	Success	Success	Success	-
Server 66	Failed	Warning	Success	Success	Success	Success	-
Server 93	Success	Failed	Failed	Success	Success	Success	Success

Hi,

Above is a sample of the data as you can see many days were successful but on the few occasions there were  failed server runs and some warnings. What I want is an interactive card that reflects the server status for that day for every single server. For example: Thursday will be a fail because Server 66 failed, Friday will also be a fail because there is a warning whereas Sunday will be a success because all servers ran successfully on the day.

 Here is an example of what I want it to look like!

[v-xiaotang]

Hi @darrennhg 

Try this, create 2 measures, 

Status = 
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _status =
    DISTINCT ( 'Table'[Value] )
RETURN
    IF (
        CONTAINS ( DISTINCT ( 'Table'[Value] ), 'Table'[Value], "Failed" )
            || CONTAINS ( DISTINCT ( 'Table'[Value] ), 'Table'[Value], "Warning" ),
        "Unsuccess",
        "Success"
    )

Color = 
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _status =
    DISTINCT ( 'Table'[Value] )
RETURN
    IF (
        CONTAINS ( DISTINCT ( 'Table'[Value] ), 'Table'[Value], "Failed" )
            || CONTAINS ( DISTINCT ( 'Table'[Value] ), 'Table'[Value], "Warning" ),
        "White",
        "#68B56B"
    )

then change the setting of Card visual,

result:

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[darrennhg]

Thank you very much! Although this helps for individual servers on the day rather than a summary for every single server combined for the day.

[v-xiaotang · Accepted solution]

Hi @darrennhg 

Thanks for your reply.

If you want to create summary for every single server, you can make adjustments on the sample file I attached, their calculation logic is similar.

Have I solved your question? Could you accept my answer as the solution? Thanks for your contribution to improve Power BI.❤️

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

can you provide data in a text format.