Counting the number of occurence of a value in a column

[choicm]

Hi. I am new to Power BI.

I have a data set like this:

Date		Question		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24		Total no. of "1"
18/09				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1		23
19/09				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	1	1	1	1	1	1	1		22
20/09				1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	0	1	1	1	1	1	1	1		22
21/09				1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	0	1	1	1	1	1	1	1		22
22/09				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1		23
23/09				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1		23
24/09				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1		23
25/09				1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1		22
26/09				1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		23
27/09				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1		23
28/09				1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	0	1	1	1	1	1	1	1	1		22
29/09				1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	0	1	1	1	1	1	1	1	1		22
30/09				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1		23

I want to count the total number of "1" in each row. When using excel, it is easy to use COUNTIF and select the range.

What formula should I use in Power BI?

Thank you.

[lbendlin · Accepted solution]

Welcome to Power BI.

When you come from an Excel background you will want to first learn how to unpivot your data to bring it into a usable format. Unlike Excel, Power BI expects narrow, long tables, with fixed columns.

From there it is simple to get the result, even without formulas. Power BI has implicit measures that can do the work for you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQN7BU0lEypBAb4JWP1QHaZEm5TQZE2WRkQLpN2EwlwiZDutlkRK94MjKmm00mdLPJlDib8JtElE1m1LEJPwbbZE6d/ESETWSUEegmE2kTGWUEeTYZk1FGkBF6sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t, #"8" = _t, #"9" = _t, #"10" = _t, #"11" = _t, #"12" = _t, #"13" = _t, #"14" = _t, #"15" = _t, #"16" = _t, #"17" = _t, #"18" = _t, #"19" = _t, #"20" = _t, #"21" = _t, #"22" = _t, #"23" = _t, #"24" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Question", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[choicm]

Thank you so much @lbendlin. But I didn't even know how to make that unpivot table. Is it easy for me to use it in Power BI service or in Desktop?

Thanks.

[lbendlin]

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.