Frustrating with Countrows and Filter

[ckserra33]

Hi everyone.  In order to troubleshoot a formula I have added a single label trying to count rows.  When I use this code, the label is blank, nothing is shown.

CountRows(
Filter(
'ExtraTasks-Stores',Value(varUser.Department) = StoreLocationNumber && Today() < TaskStartingDate))

When I break it down and use either the first half or last half of the code it works perfectly.

CountRows(
Filter(
'ExtraTasks-Stores',Value(varUser.Department) = StoreLocationNumber))

or 

CountRows(
Filter(
'ExtraTasks-Stores', TaskStartingDate < Today()))

Works perfectly!  Any help would appreciated!

[calerof]

Hi @ckserra33,

Are you making sure that in the full formula you are using TaskStartingDate < Today() - and not as you are showing it?

Cheers,

Fernando

[ckserra33]

HI @calerof 

This is what I am using.   

CountRows(
Filter(
'ExtraTasks-Stores', TaskStartingDate < Today()))

[calerof]

So this:

CountRows(
Filter(
'ExtraTasks-Stores',Value(varUser.Department) = StoreLocationNumber && Today() < TaskStartingDate))

Should be:

CountRows(
Filter(
'ExtraTasks-Stores',Value(varUser.Department) = StoreLocationNumber && TaskStartingDate < Today()
)
)

[ckserra33]

Hi @calerof    Thank you for the response but it still shows a blank in my label

[ckserra33]

Hi @calerof 

Ok so if I remove the Value() from the varuser.department and wrap StoreLocation in Text it works.  Delegable but works.  Imma have to rethink how I do the process.

&& varUser.Department = Text(StoreLocationNumber)))

Thanks for your help!

[LaurensM]

Hi @ckserra33,

As a general rule of thumb, try to avoid functions within your condition statements. The With() function is a great way to achieve this:

With(
    {wToday: Today(), wDepartmentValue: Value(varUser.Department)},
    CountRows(
        Filter(
            'ExtraTasks-Stores', 
            //or TaskStartingDate < wToday depending on which one was correct
            StoreLocationNumber = wDepartmentValue && TaskStartingDate > wToday
        )
    )
)

I hope this helps!

[ckserra33]

Hi @LaurensM .  Thank you for replying! So I tried your formula as well as the one below (I changed to a text column hoping for better results)

Both work and show the correct number now but there is a delegation warning on the date portion of the code.  

Any thoughts?  Thank you again!!

With({wToday:Today(),wLocation:varUser.OfficeLocation},CountRows(
Filter(
'ExtraTasks-Stores',StoreLocation = wLocation && TaskStartingDate < wToday)))

[LaurensM · Accepted solution]

Hi @ckserra33,

Is TaskStartingDate a DateTime column by any chance? In case it includes a time stamp, then we will have to compare it with a DateTime value instead of a Date value. Add + Time(0,0,0) to your wToday formula:

With({wToday: Today() + Time(0,0,0), wLocation...

I hope this helps!

[ckserra33]

Hi @LaurensM Thats PERFECT!!!  Thank you again for your help!