Count the number of successes/failure and combine the reasons for failure for each machine

[v-lili6-msft]

Scenario:    

Suppose I would like to  

1. Count the number of successes and failure separately 
2. Combine all reasons for failure  

for each machine. How can I achieve this requirement?  

Sample Data:  

Method1: In DAX 

1. Create a new table1-> Event =”Fail”  

• Filter out the Event column that contains “Fail”  
• Calculate the "Fail" number for each machine  
• Use &”: ”& to combine fail string and count for final Event column. 

1. Create a new table2 -> Event=Failure reasons 

• Filter out the Event columns that do not contain "Fail" and “Success” 
• Use CONCATENATEX() function to combine reasons for each machine 

1. Create a new table3 -> Event =”Success” 

• Filter out the Event column that contains “Success” 
• Calculate the count of success for each machine 
• Use &”: ”& to combine success string and count for final Event column. 

1. Use UNION() function to these three tables 

DAX Operation:  

Table 2 =
VAR _t =
    SUMMARIZECOLUMNS ( 'Table'[Machine Name], 'Table'[Event] )
VAR _t1 =
    DISTINCT (
        SELECTCOLUMNS (
            _t,
            "Machine Name", [Machine Name],
            "Event",
                "Fail" & ": "
                    & COUNTROWS (
                        FILTER (
                            'Table',
                            'Table'[Event] = "Fail"
                                && 'Table'[Machine Name] = EARLIER ( 'Table'[Machine Name] )
                        )
                    )
        )
    )
VAR _t2 =
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                _t,
                "a",
                    "Failure Reasons" & ": "
                        & CONCATENATEX (
                            FILTER (
                                _t,
                                [Event] <> "Fail"
                                    && [Event] <> "Success"
                                    && [Machine Name] = EARLIER ( [Machine Name] )
                            ),
                            [Event],
                            " // "
                        )
            ),
            "Machine Name", [Machine Name],
            "Event", [a]
        )
    )
VAR _t3 =
    SUMMARIZE (
        'Table',
        'Table'[Machine Name],
        "Event",
            "Success" & ": "
                & CALCULATE (
                    COUNT ( 'Table'[Event] ),
                    FILTER (
                        'Table',
                        'Table'[Event] = "Success"
                            && 'Table'[Machine Name] = EARLIER ( 'Table'[Machine Name] )
                    )
                )
    )
RETURN
    UNION ( _t1, _t2, _t3 )

Output: 

Method2: In Power Query 

  1. Duplicate Table named Fail 

• Remove ID column 
• Filter Event column=”Fail” 
• Select Machine Name column -> use Group By tabs -> Aggre is Count 
• Add a custom column to combine fail string and count 
• Rename Custom column and remove unnecessary columns. 

 
 2. Duplicate Table named Fail Reason 

• Remove ID column 
• Filter Event column do not equal “Fail” and “Success” 
• Use Text.Combine() function in Advanced Editor 

 
 3. Duplicate Table named Success 

     Almost the same as step1 

 4. Append these three tables 

M Operation in Advanced Editor:  

Fail table: 

let 

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZC9CsIwFEZfJWRO4db8mLjd0IhgHWybxdJBioPgVnx/U6VQSEjNcCFwvu+epO8pB6604pRRmE8lre5Ahuvx/nzRgS2EiIn2PY6PaVpBcrMmscgXXCELUyM5EOutrV2+M58wsCQEP2kBu8jCqE0i0eEL0MiIL0rAfFdA+OxXiq8fVqTGm2vIBZvzKqnjZPSlZv8PZDbfkyCCn8SfXuOu3rUdHYYP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Machine Name" = _t, Event = _t]), 

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Machine Name", type text}, {"Event", type text}}), 

    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ID"}), 

    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Event] = "Fail"), 

    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Machine Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}), 

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Event", each "Fail" &": "&Number.ToText([Count])), 

    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}) 

in 

#"Removed Columns1" 

Fail Reasons table: 

let 

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZC9CsIwFEZfJWRO4db8mLjd0IhgHWybxdJBioPgVnx/U6VQSEjNcCFwvu+epO8pB6604pRRmE8lre5Ahuvx/nzRgS2EiIn2PY6PaVpBcrMmscgXXCELUyM5EOutrV2+M58wsCQEP2kBu8jCqE0i0eEL0MiIL0rAfFdA+OxXiq8fVqTGm2vIBZvzKqnjZPSlZv8PZDbfkyCCn8SfXuOu3rUdHYYP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Machine Name" = _t, Event = _t]), 

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Machine Name", type text}, {"Event", type text}}), 

    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ID"}), 

    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Event] <> "Fail" and [Event] <> "Success") 

,   #"Grouped Rows" = Table.Group( #"Filtered Rows", {"Machine Name"}, {{"Event", each Text.Combine(List.Distinct([Event]), " // "), type text}}) 

in 

#"Grouped Rows" 

Success table 

let 

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZC9CsIwFEZfJWRO4db8mLjd0IhgHWybxdJBioPgVnx/U6VQSEjNcCFwvu+epO8pB6604pRRmE8lre5Ahuvx/nzRgS2EiIn2PY6PaVpBcrMmscgXXCELUyM5EOutrV2+M58wsCQEP2kBu8jCqE0i0eEL0MiIL0rAfFdA+OxXiq8fVqTGm2vIBZvzKqnjZPSlZv8PZDbfkyCCn8SfXuOu3rUdHYYP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Machine Name" = _t, Event = _t]), 

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Machine Name", type text}, {"Event", type text}}), 

    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ID"}), 

    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Event] = "Success"), 

    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Machine Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}), 

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Event", each "Success"&": "&Number.ToText([Count])), 

    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}) 

in 

    #"Removed Columns1" 

Output: 

Note: 

In this method, since I created three tables by duplicating, it will memory/CPU to increase somewhat. And normally, memory consumption will reduce the performance and brings more load to the model. 

So it is recommended to use Method1. 

Please check the attached file for details.  

I hope this article can help you with the similar question.  

Author: Eyelyn Qin 

Reviewer:  Icey Zhang & Lin Li 

Count the number of successes and failure and combine the reasons for failure for each machine.pbix