create variable from collection that could be blank and put a zero if blank?

[mandy4514]

So I have a variable that works

Set(
    varBuildingDailyCount,
    Filter(
        collectionDailyLog,
        FieldDate = varCurrentDate && Building = varBuilding.Value
    ).NumberPerday
)

This works to bring back the value of number per day. My issue is that sometimes that number does not exist and I need to set the value to zero. If I alter the formula with an if, I cannot seem to set the table value to zero if blank.

   Set(varDailyCount, If(IsBlank(Filter(
        collectionDailyLog,
        FieldDate = varCurrentDate && Building = varBuilding.Value
    ).NumberPerday
),0,
    Filter(
        collectionDailyLog,
        FieldDate = varCurrentDate && Building = varBuilding.Value
    ).NumberPerday
))

Any and all help would be appreciated!

[RandyHayes · Accepted solution]

@mandy4514 

To be clear, in your set formula, you are setting your variable to a table, not a value.  If you want just a value, then you need to utilize the LookUp function and not the Filter function.

ex. with LookUp and also Coalesce to provide the 0 value if blank.

Set(
    varBuildingDailyCount,
    Coalesce(
        LookUp(collectionDailyLog,
            FieldDate = varCurrentDate && Building = varBuilding.Value,
            NumberPerday
        ),
        0
    )
)

If it truly is a Table that you want in your variable, then you can implement this:

Set(
    varBuildingDailyCount,
    ForAll(
        Filter(collectionDailyLog,
            FieldDate = varCurrentDate && Building = varBuilding.Value
        ).NumberPerday,
        {NumberPerday: Coalesce(NumberPerday, 0)}
    )
)

I hope this is helpful for you.