Forum Discussion

fareedice's avatar
fareedice
Frequent Visitor
3 years ago
Solved

If else condition for RAG status

Hi

I have this below table :

 

StatusArea
RABC
ADEF
GHEG

 

I have a status column in the above Table ( Red, Amber, Green) - Always 3 rows will be there in my Table. It may vary week on Week for my respective Areas based on the work execution.

I want to derive a measure to represent the over all status based on Below conditions :

  • If the status is RAG in three Rows then over all status should be' A'
  • If the status is RRG in three Rows, i.e greater than 1 R, then over all status should be' R'
  • If the status is GGG in three Rows, then over all status should be' G'

Please advice

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi fareedice ,

     

    See if this method works for you:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclfSUXJ0claK1YGwXVzd4GwPV3el2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, Area = _t]),
    R_List = List.FindText(Source[Status], "R"),
    G_List = List.FindText(Source[Status], "G"),
    Output =
    Table.AddColumn(
        Source,
        "Output", each
        if List.Count(R_List) > 1 then "R"
        else if List.Count(G_List) = 3 then "G"
        else "A"
    )
in
    Output

     

    It basically counts the occurrences of either "R" or "G" from the column in the previous step, then applies the output grading based on the counts.

     

    Example query outputs:

     

     

     

    Pete

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi fareedice ,

     

    See if this method works for you:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclfSUXJ0claK1YGwXVzd4GwPV3el2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, Area = _t]),
    R_List = List.FindText(Source[Status], "R"),
    G_List = List.FindText(Source[Status], "G"),
    Output =
    Table.AddColumn(
        Source,
        "Output", each
        if List.Count(R_List) > 1 then "R"
        else if List.Count(G_List) = 3 then "G"
        else "A"
    )
in
    Output

     

    It basically counts the occurrences of either "R" or "G" from the column in the previous step, then applies the output grading based on the counts.

     

    Example query outputs:

     

     

     

    Pete