Forum Discussion

zhona9's avatar
zhona9
Icon for Resolver I rankResolver I
3 years ago
Solved

Line in Line and Stacked Chart not filtering by date

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 conne...
Need Help
  • zhona9's avatar
    zhona9
    3 years ago

    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.

zhona9's avatar
zhona9
Icon for Resolver I rankResolver I
3 years ago

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

danextian's avatar
danextian
Icon for Super User rankSuper User
3 years ago

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
        )
    )