DAX to change blanks to zeroes (and/or override the legend filter)

[Coriel-11]

Hi everyone,
I have two, related data tables. 'New SLA' is like this:

Asking Date	Mailbox	Emails remaining	Multiple
03-Oct-24	Training	807	1296
03-Oct-24	Digital	9	8
03-Oct-24	Shipping	0	0
04-Oct-24	Training	1321	1181
04-Oct-24	Digital	14	16
07-Oct-24	Training	1528	1583
07-Oct-24	Digital	22	21
08-Oct-24	Training	2158	1663
08-Oct-24	Digital	14	18
09-Oct-24	Training	1528	1715
09-Oct-24	Digital	57	58
09-Oct-24	Shipping	0	0

And then a 'Calendar' (i.e. date) table, which is related via 'New SLA'[Asking Date] → 'Calendar'[Date] like this:

Date	DayName	DayMn	DayMnSort
01-Oct-24	Tu	1.Oc	1001
02-Oct-24	We	2.Oc	1002
03-Oct-24	Th	3.Oc	1003
04-Oct-24	Fr	4.Oc	1004
07-Oct-24	Mo	7.Oc	1007
08-Oct-24	Tu	8.Oc	1008
09-Oct-24	We	9.Oc	1009
10-Oct-24	Th	10.Oc	1010

Originally I was trying to use a line chart and get it to look like this:
(Where the colours in the legend relate to the 'New SLA'[Mailbox] column.)

(to create this I basically had to handpick the "dates" along the x axis via the filter)

Instead, no matter how many variations on DAX measures I tried (must be 50+) I always came down to 2 basic problems. Either there would be gaps for the blue "Shipping" line; or the x axis would expand to include the weekends (where none of the fields had any data).

The reason for this is that for several dates, the Shipping mailbox does not have a row, so I either make it force every potential date in range to be a 0 if there is no data, or to put up with the blanks. I have tried using CROSSJOIN() / SUMMARIZE() / ADDCOLUMNS() to create a table within the measure, and using COALESCE(), UNION() etc.

I know I could probably create a calculated table, or fix it in PowerQuery, add the values for the x axis into the 'New SLA' table etc. But I guess having invested so much time in this I want to at least learn something.

So my question is this: Within the measure, how can I add in the extra rows/data points  so they won't get wiped out by the chart filter context? And/or is there a way to override what I'm calling the legend context, because that seems to ruin all my efforts to ALL() type commands to escape the filter context as I would do normally. How do I get it to work?

A sample file is below, featuring a couple of examples of what I have tried and failed and how it turned out.

Dropbox link to .pbix file

Many thanks,

Coriel-11

[ajohnso2]

Can you not just filter weekends out of your date table and convert any remaining blanks (Mon-Fri) to 0?

[Coriel-11]

Thanks @ajohnso2 
This is what I ended up doing in the end, but I felt a little short changed by it. Not only does it not completely cover all viable scenarios (e.g. Christmas Day), but also I feel I should somehow be able to do it without resprting to that and so I guess I'm trying to learn in the process. 
Thanks for your help,

[v-bofeng-msft]

Hi @Coriel-11 ,

Would you like to use DAX to replace blank values with 0 so they can be displayed in the line chart, and also filter out weekends?If so , please try:

SLA for Chart = If(SELECTEDVALUE('Calendar'[DayName]) in {"Sa","Su"},BLANK(),
IF(SUM('New SLA'[Multiple])<0,
    0,
    DIVIDE(
        SUM('New SLA'[Multiple]),
        SUM('New SLA'[Emails remaining]),
    0)
)+0)

Best Regards,
Bof

[Coriel-11]

Hi @v-bofeng-msft 
Thanks for your reply.
I hadn't thought of doing it that way – in fact I've not used  in {"A","B"} before. I went away, looked that up and even then used it in something else on Friday, so thanks for that.

I guess this does similar to the filter but it's inside the measure, which I like.

Part of me is still curious, though, as to how you can get it to do it on the basis of there being no values for a particular date. That's not just because then it would also cover national holidays etc but also because I'm curious as to why I can't work outside the legend context.

[_AAndrade]

Hi @Coriel-11,

This is happening because you're using a column that would be considered as categorical type on X-Axis.

If you use the Date column you can change this type to Continuos and the output would be this:

If you pretend to use DayMn on the X-Axis change tour Dax measure to this:

And the final output woul be the same:

[Coriel-11]

Thanks @_AAndrade ,
I appreciate your help. I had tried the first method, but wanted to figure out why it doesn't work. Unfortunately though, when I try your second method (and I remove the DayName filter on the visual) I still get this (with the weekends showing when there is no data), I guess because where I want it to be blank is now a zero.

It just seems impossible to force in 0s when there's no data for just that stream.
Do you have any other ideas?
Many thanks for taking the time,
Coriel-11

[_AAndrade]

Hi @Coriel-11,

I didn't understand what you're looking for. Could you please explain in more detail what is the desired output?
Because I thought you want to replace blank values to 0 and it was what I did in my example.

[Coriel-11]

Hi @_AAndrade 
Thanks for your message and sorry if I've not been very clear.

I am trying to replace blank values with zeroes, but only in those cases where there is at least 1 non-blank value for the date. So at weekends when all three values are blank, I want them to stay that way, but if (say during the week Training is 123 but there's nothing for shipping, I want it to show 0, not be blank).

I guess I'm particularly interested in how that can be done within a measure (rather than using a visual filter), and especially how the filtering effect of the legend can be overcome because something like the ALL() function doesn't seem to be able to do that.

Not sure if that's any clearer, apologies if not.
Thanks again