CountIf Measure

[Madmaxigail]

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 day (rows).  I have multiple time of day columns with values ranging from 1 to 5.  I want to know how many times in each day (row) 1 incident occurs at a timeframe (column), how many times in each day (row) 2 incidents occurs at a timeframe (column), and so on. 

In Excel I can simply create 5 "countif" columns for each of the 5 values across all columns.

Here is a sample table...

Date	10:00	11:00	12:00	13:00
January 1	1	1	1	2
January 2	1	2	2	4
January 3	2	5	3	1

So I would like to generate a measure that would count how many times 1 appeared on Jan 1.  In this example above it would return 3 as the answer.  And then I would create more measures for the other numbers of incidents (2, 3, 4, and 5) across all times for each date.  

Is this a possibility?  I actually have nearly 100 columns for each date in my dataset (every 15 minutes).

Thanks in advance.

[Madmaxigail]

Sorry folks, I skipped a step in my dataset and need to go back to transform my data as I indicated in my example.  I am going to reframe my question.

[v-yueyunzh-msft]

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

[Ashish_Mathur]

Hi,

In the Query Editor, right click on the first column and select "Unpivot Other Columns".  In the matrix visual, drag Date to the row labels and Value to the Column labels.  Write this measure

Count = countrows(Data)

Hope this helps.