Make blanks show 0 only for items that have data in other columns

[CCTADA]

I've seen different workarounds but I haven't found what I'm looking for yet so I'm hoping to get some assistance. What I'm looking at is a location and number of job opening during a set time period that is determined by using a slicer.

This is what I have now for the openings based on the time frame and location:

Total Openings =
CALCULATE( SUM( JOB_HISTORY[Count Openings] ),
FILTER(VALUES( job_history[start_datetime] ), job_history[start_datetime] <= MAX( 'Calendar_Dates'[Date]) ),
FILTER(VALUES( job_history[end_datetime] ), job_history[end_datetime] >= MAX( 'Calendar_Dates'[Date] ) )
)

and this is what it returns in my matrix:

I'm looking to have zeroes in place of the blanks so I tried this:

Total Openings No Blanks = 
IF (
CALCULATE( SUM( JOB_HISTORY[Count Openings] ),
FILTER(VALUES( job_history[start_datetime] ), job_history[start_datetime] <= MAX( 'Calendar_Dates'[Date]) ),
FILTER(VALUES( job_history[end_datetime] ), job_history[end_datetime] >= MAX( 'Calendar_Dates'[Date] ) ))
= blank(), 0,
CALCULATE( SUM( JOB_HISTORY[Count Openings] ),
FILTER(VALUES( job_history[start_datetime] ), job_history[start_datetime] <= MAX( 'Calendar_Dates'[Date]) ),
FILTER(VALUES( job_history[end_datetime] ), job_history[end_datetime] >= MAX( 'Calendar_Dates'[Date] ) ))
)

And that returns this:

The problem is that since Hawaii and Alaska now have values (0), it's showing me the line. This isn't so bad on this table, but when I look at all states there's a lot of lines I don't need. I've tried the "+0" but I wasn't sure where to add it to my calculate formula so I kept getting errors. Unfortunately I can't change anything with the original DAX as that is the only thing that will calculate correctly.

Thoughts on how to add zeroes to the blanks, but not add in locations that have no data for the time period that is chosen? 

[Anonymous · Accepted solution]

Hi @CCTADA ,

Fisrt, Consider creating a separate table for the [Location] field.

New Table = DISTINCT('Table'[Location] )

Don't create relationships.

Use 'New Table'[Location] as matrix's row value.

And create a new measure like:

MEASURE =
VAR __cur_location =
    SELECTEDVALUE ( 'New Table'[Location] )
VAR __result =
    CALCULATE (
        SUM ( JOB_HISTORY[Count Openings] ),
        FILTER (
            VALUES ( job_history[start_datetime] ),
            job_history[start_datetime] <= MAX ( 'Calendar_Dates'[Date] )
        ),
        FILTER (
            VALUES ( job_history[end_datetime] ),
            job_history[end_datetime] >= MAX ( 'Calendar_Dates'[Date] )
        ),
        'Table'[Location] = __cur_location
    )
RETURN
    __result

use the measure as matirx's value and the output:

Best Regards,
Gao
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 -- China Power BI User Group

[RossEdwards]

Would something like this work?

Total Openings No Blanks = var hasData = CALCULATE( 
    SUM( JOB_HISTORY[Count Openings] ),
    ALLSELECTED(JOB_HISTORY)
)
var output = SUM( JOB_HISTORY[Count Openings] )
RETURN
SWITCH(
    TRUE,
    AND(ISBLANK(output), ISBLANK(hasData)), BLANK(),
    ISBLANK(output), 0,
    output
)
    

[CCTADA]

Where is this part supposed to go? It's what determines the count for specific dates. 

FILTER(VALUES( job_history[start_datetime] ), job_history[start_datetime] <= MAX( 'Calendar_Dates'[Date]) ),
FILTER(VALUES( job_history[end_datetime] ), job_history[end_datetime] >= MAX( 'Calendar_Dates'[Date] ) ))

 Otherwise, I'll go play around and see what happens. Thanks.

[RossEdwards]

That code you showed does not need to exist because its job is done by ALLSELECTED.  ALLSELECTED expands the filter context outside of the filtering from the visual itself out to what was selected by slicers or any filters set on the page/report.