Measure to show zero if fact table contains no value for date and location

[andrew_bouland]

I am working on a report to show the number of employees that are present in a given location by date.

I have a fact Table 'CheckinDaily' has columns for Date, Location, Users and it only contains values where the Users count is 1 or greater.  I would like to be able to create a measure/custom column to show values for all possible locations for any given date where Users value is 0 if there is not an entry in the fact table for that location for a date. 

Fact table is related to a date table 'Date' many to one that has values for every calendar date from 2020-2030

Fact table is also related many to one to a Location table 'OfficeAbbr' that has values for all of our various offices

Fact table example:

Date	Location	Users
1/28/2025	ATL	1
1/28/2025	STL	20
1/29/2025	DEC	2
1/29/2025	SPG	4
1/30/2025	DAL	25

Location table example:

Location

ATL

BOS

BOS2

CHT

CIN

COS

DAL

DEC

DEN

SPG

STL

What I'd like to see:

1/28/2025	ATL	1
1/28/2025	BOS	0
1/28/2025	BOS2	0
1/28/2025	CHT	0
1/28/2025	CIN	0
1/28/2025	COS	0
1/28/2025	DAL	0
1/28/2025	DEC	0
1/28/2025	DEN	0
1/28/2025	SPG	0
1/28/2025	STL	20
1/29/2025	ATL	0
1/29/2025	BOS	0
1/29/2025	BOS2	0
1/29/2025	CHT	0
1/29/2025	CIN	0
1/29/2025	COS	0
1/29/2025	DAL	0
1/29/2025	DEC	2
1/29/2025	DEN	0
1/29/2025	SPG	4
1/29/2025	STL	0

etc

I have tried the following meaure:

Daily Headcount by Location =

IF (

    CALCULATE(COUNTROWS('CheckinDaily'), 'CheckinDaily'[Date] = ('Date'[Date]), 'CheckinDaily'[Location] = ('Location'[Location])) = 0,

    0,

    SUM('CheckinDaily'[Users])

)

when attempting to create the measure, I get an error: 

"The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."

[Anonymous]

Hi, @andrew_bouland 

Based on the information you gave, you can try the following dax to achieve your need, if this doesn't fit your needs, you can give pbix files without sensitive data for testing (it seems you have Date table in your raw data).

NewTable 2 = 
ADDCOLUMNS(
    CROSSJOIN(
        DISTINCT('Table 1'[Date]),
        'Table 2'
    ),
    "Users", 
    VAR CurrentDate = [Date]
    VAR CurrentLocation = [Location]
    RETURN
    CALCULATE(
        SUM('Table 1'[Users]),
        'Table 1'[Date] = CurrentDate,
        'Table 1'[Location] = CurrentLocation
    )
)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[andrew_bouland]

Hello, thank you for the suggestion - having a little trouble still - getting an error with the VAR statements at the moment.  If I don't specify table for Location then it tells me it cannot find Location reference.  If I do specify table for Date and/or location then I get an error "A single value for column 'Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Here's current code I am trying:

Table 2 =

ADDCOLUMNS(

    CROSSJOIN(

        DISTINCT('Table 1'[Date]),

        'Table 2'

    ),

    "Users",

    VAR CurrentDate = 'Date'[Date]

    VAR CurrentLocation = 'Location'[Location]

    RETURN

    CALCULATE(

        SUM('Table 1'[Users]),

        'Table 1'[Date] = CurrentDate,

        'Table 1'[Location] = CurrentLocation

    )

) +0

pbix same file here

Posted this before seeing your PBIX example - in my case we've got a discrete date table that we're referencing as our fact table (table 1) might have gaps where a calendar date has no value(s) present.  We'd want to make sure that we're getting zeros for any calendar date per location where there isn't a value reported in our fact table for that specific date. 

[Anonymous · Accepted solution]

Hi, @andrew_bouland 

You can try the following dax to achieve your need.

Note that your table one has blank data rows, which will affect your output, so it's best to optimize your data.

Table 2 = 
ADDCOLUMNS(
    CROSSJOIN(
        DISTINCT('Table 1'[Date]),
        'Location'
    ),
    "Users", 
    VAR CurrentDate = [Date]
    VAR CurrentLocation = 'Location'[Location]
    RETURN
    CALCULATE(
        SUM('Table 1'[Users]) + 0,
        'Table 1'[Date] = CurrentDate,
        'Table 1'[Location] = CurrentLocation
    )
) 

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[andrew_bouland]

Thanks so much, I was able to get it working as I needed!

Andrew

[AilleryO]

Hi,

I would just add a little something to your formula to answer exactly the question :

Add +0 by the end of formula if you want 0 and not blank as a result.

Thanks for your answer