NULL to 0 conversion fails when Category is added to line+column chart

[MatthiasVC]

I'm building a dashboard with Stafplanning. People have complained that when filtering certain departments the line value does not show correct values (namely in in-between periods where it should be 0, it just links periods before and after).
For the departments where there is an actual 0 in the source table, this is not an issue, however I try to create a zero-value for some categories and it works until I add the category.

Problem:
I have the following data: (basically column "Category" is department, Colum "Type" is whether the hours are originally Planned planned (Base), Actual or Current Planned hours.

Category	Type	Month	FTE
A	Base	01-12-2024	0
A	Base	01-01-2025	10
B	Base	01-01-2025	12
A	Base	01-02-2025	0
A	Base	01-03-2025	7
B	Base	01-03-2025	6
A	Base	01-04-2025	0
A	Actual	01-01-2025	9
B	Actual	01-01-2025	12
A	Planned	01-02-2025	0
A	Planned	01-03-2025	8
B	Planned	01-03-2025	10

I then have following measures to extract the proper information:

Actual FTE = CALCULATE(Sum('Stafplanning_FTE'[FTE]),FILTER('Stafplanning_FTE','Stafplanning_FTE'[Type]="Actual"))
Base FTE = CALCULATE(Sum('Stafplanning_FTE'[FTE]),FILTER('Stafplanning_FTE','Stafplanning_FTE'[Type]="Base"))
Planned FTE = CALCULATE(Sum('Stafplanning_FTE'[FTE]),FILTER('Stafplanning_FTE','Stafplanning_FTE'[Type]="Planned"))
Actual/Planned = [Actual FTE]+[Planned FTE]

Now the problem is that I want Actual/Planned to be shown on columns and Base FTE on a line.
The base FTE should show a dip in feb/25 because nothing was planned there. It does it correctly as long as "A" is in the selection, however when only "B" is in the selection, the dip disappears and it connects jan/25 and march/25 in a straight line.
Effects of filters on the Graph with/without Zero correction
I tried to correct this using a measure that tries to ignore the "category filter" temporarily, checks if the date is between the minimum and maximum date within range that has data (because I don't want Dec/24 and Apr/25 in my chart) and then convert NULLs to zeroes within that range. (see below DAX). The measure seems to work in a table and in the chart WITHOUT column categories, however as soon as the column category is turned on again the dip disappears again (see center graph in the bottom).

Base FTE OR 0 =
VAR CurrentMonth =
    CALCULATE ( MAX ( Date_Table[Month] ), ALL ( Stafplanning_FTE[Category] ) ) -- Get MIN and MAX week from all selected activities
VAR MinSelectedMonth =
    CALCULATE (
        CALCULATE (
            MIN ( Stafplanning_FTE[Month] ),
            ALL ( Stafplanning_FTE[Category] ),
            FILTER ( Stafplanning_FTE, Stafplanning_FTE[FTE] > 0 )
        ),
        ALL ( Date_Table[Month] )
    )
VAR MaxSelectedMonth =
    CALCULATE (
        CALCULATE (
            MAX ( Stafplanning_FTE[Month] ),
            ALL ( Stafplanning_FTE[Category] ),
            FILTER ( Stafplanning_FTE, Stafplanning_FTE[FTE] > 0 )
        ),
        ALL ( Date_Table[Month] )
    )
VAR Base = [Base FTE]
VAR ReturnValue =
    IF ( NOT ISBLANK ( Base ), Base, 0 )
RETURN
    IF (
        CurrentMonth >= MinSelectedMonth
            && CurrentMonth <= MaxSelectedMonth,
        ReturnValue,
        BLANK ()
    )

How Can I resolve this without having to resort to adding lots of zeros in my original table?
Kind regards, 
Matthias

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins.

In your scenario this means that the Date table needs to be disconnected.

[MatthiasVC]

that would indeed help however I was hoping for a solution without changing my model, only my DAX (and I found one)

[MFelix]

Hi @MatthiasVC ,

Try to add a +0 on the the original measure:

Base FTE = CALCULATE(Sum('Stafplanning_FTE'[FTE]),FILTER('Stafplanning_FTE','Stafplanning_FTE'[Type]="Base")) + 0

This should convert the value from null to 0

[MatthiasVC · Accepted solution]

MFelix's answer allowed me to stumble into the solution.
I was focused on changing the DAX for 'Base FTE or Zero' however that DAX was not incorrect and already turned the NULL to 0 where appropriate.
The solution was to basically do the same for Actual/Planned:

Actual/Planned = 
VAR CurrentMonth = CALCULATE(MAX(Date_Table[Month]),ALL(Stafplanning_FTE[Category]))
-- Get MIN and MAX week from all selected activities
VAR MinSelectedMonth = 
    CALCULATE(
            CALCULATE(
                MIN(Stafplanning_FTE[Month]),ALL(Stafplanning_FTE[Category]),
                FILTER(Stafplanning_FTE,Stafplanning_FTE[FTE]>0)
            ),
            ALL(Date_Table[Month])
        )
VAR MaxSelectedMonth = 
    CALCULATE(
            CALCULATE(
                MAX(Stafplanning_FTE[Month]),ALL(Stafplanning_FTE[Category]),
                FILTER(Stafplanning_FTE,Stafplanning_FTE[FTE]>0)
            ),
            ALL(Date_Table[Month])
        )
VAR APValue = [Actual FTE]+[Planned FTE]
VAR ReturnValue = 
    IF(
        NOT ISBLANK(APValue),
        APValue,
        0
    )
RETURN
IF(CurrentMonth >= MinSelectedMonth && CurrentMonth <= MaxSelectedMonth,ReturnValue,BLANK())

Apparently the way the Column+line chart works is like this:
=> If there is no column legend: calculate for all X-axis values ALL column+Line chart values. Drop the data where everything is NULL
=> If there is a column legend:  calculate all for all X-axis values/column legend combo ALL column values. Drop the data where everything is NULL. Then calculate all Line values.

Or at least that's how I interpret this result.
SO in order to have the 0 value when a column legend is selected, I have to make sure the column also has a 0 value and not only NULLS.

[MFelix]

Hi @MatthiasVC ,

Glad you were able to figure it out. Don't forget to mark the correct answer so it can help others.