Countifs in powerbi

raddy

You can use this

Please see attached file for steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfIvSM0DUo5FyRmZSrE6KIK+MaUGBkZmrnBx55z84tQUFOVOyMIoGpywme6MxZRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Buildings = _t, Status = _t, Trade = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Buildings", type text}, {"Status", type text}, {"Trade", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Buildings"}, {{"AllRows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows([AllRows],each [Status]="Open" and [Trade]="Archi")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Archi-Open", each Table.RowCount([Custom])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Table.SelectRows([AllRows],each [Status]="Open" and [Trade]="M&E")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "M&E Open", each Table.RowCount([Custom.1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom.1", "Custom", "AllRows"})
in
    #"Removed Columns"