How to count how many times a record has a true value

[gbpbi]

Hello,

I have a physical inventory document that I am being asked to incorporate checks to see how many flags each record sets off. The spreadsheet version looks like this:

I am trying to figure out how to tally how many of the flags (column L:P) are reached per row. 

[Ashish_Mathur]

Hi,

First and foremost, in Power Query, you should select all columns other than the last 5 and click on "Unpivot Other Columns".

[Anonymous · Accepted solution]

Hi @gbpbi 

Based on your needs, I have created the following table.

You can return "1" with the "RECOUNT" tag and the rest with null values, and then add up the returned values.

Count = 
VAR _50percent = IF(SELECTEDVALUE('Table'[+ / - 50%])= "RECOUNT",1,BLANK())
VAR _any_change = IF(SELECTEDVALUE('Table'[Any change])= "RECOUNT",1,BLANK())
var _over1000 = IF(SELECTEDVALUE('Table'[Over 1000])= "RECOUNT",1,BLANK())
var _From0tovalue = IF(SELECTEDVALUE('Table'[From 0 to value])= "RECOUNT",1,BLANK())
var _Fromvalueto0 = IF(SELECTEDVALUE('Table'[From value to 0])= "RECOUNT",1,BLANK())

RETURN
_50percent+_any_change+_From0tovalue+_Fromvalueto0+_over1000

Result:

Best Regards,

Jayleny

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

[JP_Wisco]

Depending on your model needs, I would use power query to split your spreadsheet into two tables, one named warehouse_orders and the other named recount_alerts.

1. The warehouse_orders query would contain all of your data except the RECOUNT columns.
2. The recount_alerts table would contain your WAREHOUSE ORDER column and all of your RECOUNT columns.
   • Select the WAREHOUSE ORDER column and unpivot other columns.
   • You'll end up columns [WAREHOUSE ORDER], [ATTRIBUTE], & [VALUE].
   • From here you could load to your model, add a relationship between the two tables, and write a simple DAX measure using COUNT. A table visual by warehouse order ID and your count measure should produce the result you are looking for.
   • Or, you could stay in Power Query and use a GroupBy transformation on your recount_alerts table, specifying a Count aggregation. The resulting table would tell you how many alerts had been set off for each WAREHOUSE ORDER. You could then merge the recount_alerts table into your warehouse_orders table, effectively reducing the multicolumn RECOUNT columns into a single count. You do lose the description of the criteria that triggered the alert though.