Line in Line and Stacked Chart not filtering by date

[zhona9]

I have a line and stacked chart which used to just show the markers (round dots). I wanted to connect them with a line. I googled past solutions and added "+0" to my measure to make the markers connected by lines. However once I did this, the chart is no longer filtering by the reported date in my slicer.

The measure in my line and stacked chart is as follows:

InvestigationRequired = CALCULATE(COUNT(g360_Incidents[incident_number]), KEEPFILTERS(g360_Incidents[is_an_investigation_required]="Yes")) + 0

See the chart now:

How can I fix this? 

Thank you.

[zhona9 · Accepted solution]

Thanks for helping. In the end this dashboard was too complex to build with the existing datasource. I had to create an intermediate datasource using SQL and excel macro to reflect the data. Somehow when I did that, the visual showed correctly without having to use measures. It is probably to do with the fact that the new table I generate shows a "0" when the value is 0. So on hindsight for others, a new column to convert blank values to 0 would have worked too.

[danextian]

Hi @zhona9 ,

Adding 0 after a measue  returns 0 even if a date has no actual value when using a separate dates table. Try using a condition so the measure doesn't return 0 when there's no actual value or when the dates are not within min and max of dates with values.  See which one below works for you.

=
IF ( [measure] <> BLANK (), [measure] + 0 )

=
VAR MinDateWithValue =
    //min date in the current filter context
    CALCULATE (
        MIN ( Dates[Date] ),
        FILTER ( ALLSELECTED ( Dates ), SUM ( Fact[Value] ) <> BLANK () )
    )
VAR MaxDateWithValue =
    //max date in the current filter context
    CALCULATE (
        MAX ( Dates[Date] ),
        FILTER ( ALLSELECTED ( Dates ), SUM ( Fact[Value] ) <> BLANK () )
    )
RETURN
    CALCULATE (
        [measure] + 0,
        FILTER (
            dates,
            dates[Date] >= MinDateWithValue
                && dates[Date] <= MaxDateWithValue
        )
    )

=
VAR MinDateWithValue =
    CALCULATE (
        MIN ( Dates[Date] ),
        FILTER ( ALLSELECTED ( Dates ), SUM ( Fact[Value] ) <> BLANK () )
    )
VAR MaxDateWithValue =
    CALCULATE (
        MAX ( Dates[Date] ),
        FILTER ( ALLSELECTED ( Dates ), SUM ( Fact[Value] ) <> BLANK () )
    )
RETURN
    IF (
        SELECTEDVALUE ( DAtes[Date] ) >= MinDateWithValue
            && SELECTEDVALUE ( DAtes[Date] ) <= MaxDateWithValue,
        [measure] + 0
    )

[zhona9]

Hi,

Thanks for the fast reply.

I tried this:

InvestigationRequired = 
var countinvestigation = 
CALCULATE(COUNT(g360_Incidents[incident_number]), KEEPFILTERS(g360_Incidents[is_an_investigation_required]="Yes")) 
return 
If (countinvestigation <> blank(), countinvestigation + 0)

However it brings me back to square one, I still don't see a continuous line:

Ideally I want to see something like this (from an excel chart):

I do want to see the line go down to zero, but I only want it displayed within the filtered reported dates.

[danextian]

Have you tried the other measures? I don't have sample data model to test the  measures.

[zhona9]

For using the min/max date, is there anyway to reference the min/max date selected using the slicer?

[danextian]

Hi @zhona9 ,

You need to be able to tell the measure what are the min and max dates that have values based on the current filter context so even if your dates table has dates from before and after  the dates in your fact  table with values, the filter will be restricted to those.  That's the expected behaviour of my second solution. However  after several tests, adding 0 even within the measure still returns zero even when the date is not within the range set. The workaround is to create a helper (calculated) column in your dates table that contains 0. Sum of Values2  in the screenshot below is the result of using the second formula.

New formula would be something like:

Sum of Values3 =
VAR MinDateWithValue =
    CALCULATE (
        MIN ( Dates2[Date] ),
        FILTER ( ALLSELECTED ( Dates2 ), [Sum of Values] <> BLANK () )
    )
VAR MaxDateWithValue =
    CALCULATE (
        MAX ( Dates2[Date] ),
        FILTER ( ALLSELECTED ( Dates2 ), [Sum of Values] <> BLANK () )
    )
RETURN
    CALCULATE (
        SUM ( 'DataTable'[Values] ) + SUM ( Dates[0] ),
        FILTER (
            Dates2,
            Dates2[Date] >= MinDateWithValue
                && Dates2[Date] <= MaxDateWithValue
        )
    )