Forum Discussion

android1's avatar
android1
Icon for Post Patron rankPost Patron
9 years ago

Count Columns that don't contain zero

Hi,   Trying to count the number of weeks which have been invoiced. In attached image, Week numbers are at the top (Wk 3 - 21).   The values (14.00, 17.50 etc are the column [Visits_NoHrs])   ...
Need Help
android1's avatar
android1
Icon for Post Patron rankPost Patron
9 years ago

Maybe I'll state exactly what 'm trying to do.

 

New employees start working at any week of the year.

 

E.g

 

I have a worker who starts in week 10. They generate [Visits_NoHrs] (column directly from DB) from week 10 onwards.

Say they are off in Week 15 (so no [Visits_NoHrs] generated in Week 15). Back in Wk 16

& work Wk 17, 18, 19 ,20. In Total they have hours in 10 weeks.  I want to count these weeks.

So below I would have 8 as they generated [Visits_NoHrs] in 8 weeks.

 

 

I have this calculated column - Week = WEEKNUM([Visits_Date],21) which returns the Week of the year.

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
9 years ago

android1

 

thanks for the info.  you should be able to slice the data any which way so even with the week num, your measure should work.

 

i have created a similar scenario and this definitely works.  what are you getting with this?    There is obviously something else going on if the below doesn't work.

 

I am working on the premise here that 

vw_PivotVisitsInvoiced[Visits_NoHrs] is a decimal

 

all rows = countrows(vw_PivotVisitsInvoiced)   what do you get back with this?

 

the measure as previously stated should work when you do this

 

no of rows  with hours= calculate(countrows(vw_PivotVisitsInvoiced), vw_PivotVisitsInvoiced[Visits_NoHrs] > 0)

 

if it doesn't there is something else in the data going on?  

 

eg.

 

this is the data i created

 

 

this works perfectly my measure here is

Rows > 0 = CALCULATE(COUNTROWS(Apartments),Apartments[Hours] > 0)

 

so i am not understanding why your data is not calculating properly.  You might need to dig a big deeper into the data.