How to force DAX measure to 0 in the same filter context

[fbura]

I have a measure calculating the sales amount and i'm placing it insiede a line chart with months on the x-axis, a field coming from a table called 'CALENDAR_TREND'. The chart is filtered by a slicer with months, field coming from another table called 'CALENDAR' linked to my previous table 'CALENDAR_TREND'. I want my chart to show values of sales amount, when the measure returns blank, as zeros but doing so mutates the chart filter context showing all the month of a year and placing 0 whenever the measure is blank or the month is outside the range selected with my month slicer.  I just want the chart to show the range of months i selected with those having blank sales amount forced to zero. I already tried using ALLEXCEPT but i was not able to solve my problem.

Example:

I want the value of Sales PY to be forced as 0 in the month of August. If i put a new DAX measure usign ALLEXCEPT and add +0 to the calculation of the sales amount, i'll get a chart with months from Jan to Dec even if i filter those values out.

Any clues will be appreciated, THANK YOU! 🙂

[Anonymous]

Hi @fbura ,

I think the [Date] column in X axis should come from a continuous Date table. Then if your [Measure]+0 will make your visual expand.

Please try code as below.

Add Range for measure =
VAR _MEASURE = [Measure] + 0
VAR _RANGESTART =
    MINX ( ALL ( 'DataTable' ), 'DataTable'[Date] )
VAR _RANGEEND =
    MAXX ( ALL ( 'DataTable' ), 'DataTable'[Date] )
RETURN
    IF (
        MAX ( 'Date'[Date] ) >= _RANGESTART
            && MAX ( 'Date'[Date] ) <= _RANGEEND,
        _MEASURE
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rupak_bi]

I think if else function may solve your purpose. If not, please share sample data.