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

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.

6 Replies

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

    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's avatar
      zhona9
      Icon for Resolver I rankResolver I
      3 years ago

      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's avatar
        danextian
        Icon for Super User rankSuper User
        3 years ago

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

  • zhona9's avatar
    zhona9
    Icon for Resolver I rankResolver I
    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.