How to identify pattern that leads to a specific outcome??

[Mahmoud23]

If I have data from an experiment where the results are either red or green, I want to identify the most common pattern that leads to a specific outcome based on these results. For example, if the results are red three times in a row, the fourth result is green 80% of the time. I want to deduce these patterns and probabilities. How can I do this while ignoring all external factors, assuming that the data is just a single column containing the experiment's result—either red or green?

[danextian]

Hi @Mahmoud23 ,

You can use the Group By feature in Transform tab and add GroupKind.Local in the generated formula so the grouping is for every change in the column being grouped - not for every distinct value in that column.

= Table.Group(#"Changed Type", {"Value"}, {{"Grouped", each _, type table [Date=nullable date, Value=nullable text]}}, GroupKind.Local)

After grouping, you can then add an Index column to create a new group and add a new column to count the rows the in the tables in Grouped column.

This is the final result

Sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdJLCoAwDIThu3QtmEwf1qV6jOL9r6FSkOoPXX1MwlDSWjCf7ydTClPYwjndpIH2TnGgo1MiZQ4WphZSZYkVu9ww6E4SKWK9J1Imsb2zvVcS28tITmJ7Rab492J7FRLbq3L9isFoJP79c0z6kQba3mP6pxJT+UvnBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Value"}, {{"Grouped", each _, type table [Date=nullable date, Value=nullable text]}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Group", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "RowCount", each Table.RowCount([Grouped]),Int64.Type),
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Added Custom", "Grouped", {"Date"}, {"Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Grouped",{"Group", "Value", "Date", "RowCount"})
in
    #"Reordered Columns"

Tbh, I am interested to see how this can be done using DAX but I am too occupied to think of a logic.

[Greg_Deckler]

@danextian Now that you mention it, Cthulhu could be an option to help identify patterns, it essentially identifies streaks in the data. Cthulhu - Microsoft Fabric Community

[danextian]

Thanks for this @Greg_Deckler . Honestly, it would probably take me days to come up with a DAX solution...if I could. 😅

[Greg_Deckler]

@Mahmoud23 Sounds like a case for machine learning. Not sure there is much pattern recognition built into Power BI beyond the Key Influencers visual which can be hit or miss. Any chance you have sample data?