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 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...

Date10:0011:0012:0013:00
January 11112
January 21224
January 32531

 

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.

Need Help

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.

  • 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

  • Madmaxigail's avatar
    Madmaxigail
    Frequent Visitor
    3 years ago

    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.