Count "1" values in a column

[Anonymous]

Hi All,

I've spent far too many hours today trying to figure out what is probably a simple thing for you all...

I have a column in PBI that has either 0 or 1 in it for 200+ rows.  I want to count the "1"'s.  I've tried allsorts and been through all of the questions and answers here but they are far more complicated than this.

The column with the 0 and 1 in is "Start 28d" - please help? This would be simple in Excel: countif(range > 0) or something.

Thanks,

Martin

[ryan_mayu · Accepted solution]

@Anonymous 

if the column only has two values,0 and 1, what about use SUM ? The result of SUM is the result of counting 1.

[Anonymous]

Thanks for this - I should have gotten it, but my mind was set on 'count'.  All working as it should - thank you 🙂

[ryan_mayu]

you are welcome

[serpiva64]

Hi,

try 

Measure = calculate(count('Table'[Column1]),'Table'[Column1]=1)

[Anonymous]

Thanks! This worked for one of them (start dates) but gives me an error when trying to replicate for finish dates?

Finish 28d = IF(Task[Finish Next Period] > -28 && Task[Finish Next Period] < 0, 1, 0)
Finish 28d Qty = CALCULATE ( count (Task[Finish 28d] ),Task[Finish 28d] = 1 )

Start 28d = IF(Task[Start Next Period] > -28 && Task[Start Next Period] < 0, 1, 0)
Start 28d qty = CALCULATE ( COUNT ( Task[Start 28d] ), Task[Start 28d] = 1 )

[serpiva64]

Hi,

i don't think it's a problem of the measure.

Task[Finish Next Period] or Task[Start Next Period] are calculated columns?

[Anonymous]

Finish Next Period = Task[ReportDate] - Task[Finish]

Start Next Period = Task[ReportDate] - Task[StartDate]

[Anonymous]

Yes they are calculated.