Forum Discussion
Resolver ILine in Line and Stacked Chart not filtering by date
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.
Super UserHi 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
)
zhona9Hi,
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.