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
)
Resolver IHi,
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 ) )