Forum Discussion

Madmaxigail's avatar
Madmaxigail
Frequent Visitor
3 years ago

CountIf Measure

I may just be searching incorrectly, but I am looking to create a measure that would function like the "count if" function in Excel.   I am tracking number of incidents across time (columns) per da...
Need Help
v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi , Madmaxigail 

Here are the steps you can refer to :

(1)My test data is the same as yours.

(2)We can unpivot the table in Power Query Editor, you can put this in the "Advanced Editor":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlQwVNJRQsZGSrE6CFkjuCgEm6DIGkNFTYHYGKwyNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"10:00" = _t, #"11:00" = _t, #"12:00" = _t, #"13:00" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"10:00", Int64.Type}, {"11:00", Int64.Type}, {"12:00", Int64.Type}, {"13:00", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Time"}})
in
    #"Renamed Columns"

Then we can get this table:

 

(3)Then we just need to put the filed on the Matrix table , we will meet your need ,the result is as follows:

 

Best Regards,

Aniya Zhang

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