Forum Discussion
Conditional filtering date ranges - Help with import
Hey all.
Let me just start out by saying i am no expert in Dax/Power query, so if there is an obvious solution to my problem, i apologize.
I have made a custom visual where I import data as a table, so I get each unique row. This is needed because I do some specific calculations on the data that is not directly available in power bi.
My source is a single table containing 3 different time series.
Time series 1 (T1) goes from 01-01-2017 to (now+15 days).
Time series 2 (T2) goes from 01-01-1940 to 01-01-2015.
Time series 3 (T3) goes from 01-01-1965 to 01-01-2016
T1 is the ‘live’ time series on which the user filters.
A user selects an date interval that says 01-03-2017 to 13-03-2017. The data that I need to get into my visual should be something like this:
T1: gets each row that has a date in the specific range.
T2: get each row that has the same day of year. IE get 01-03-1940, 01-03-1945, 13-03-1940, 13-03-1945 etc.
T3: same as T2, except the time span is +/- 10 days. So each row from 19-02 to 23-03
My problem is T3. I don’t know how to expand the date range for that time series.
Is this even possible through power bi or should I look into the data source and see if I can get that changed ?
And sorry if this question is a bit cryptic 😊
Hi aristen,
So if the user selects 10.feb to 15.feb i would still get values from FactMeteorologyTimeseries from 01.Jan and forward.
Do you know why this is ?
Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.
In your scenario, you should use the formula to create a measure similar like below, then show the measure on the Table/Matrix visual with the corresponding columns from your table(or use the measure as visual level filter with Expand Date for T3 is not blank ). :smileyhappy:
Expand Date for T3 = VAR minDateSelected = DimDate[min] VAR maxDateSelected = DimDate[max] VAR tenDaysPrevious = minDateSelected - 10 VAR tenDaysAfter = maxDateSelected + 10 RETURN COUNTROWS ( FILTER ( 'dpv FactMeteorologyTimeSeries'; 'dpv FactMeteorologyTimeSeries'[ForecastTime].[Date] >= DATE ( YEAR ( 'dpv FactMeteorologyTimeSeries'[ForecastTime].[Date] ); MONTH ( tenDaysPrevious ); DAY ( tenDaysPrevious ) ) && 'dpv FactMeteorologyTimeSeries'[ForecastTime].[Date] <= DATE ( YEAR ( 'dpv FactMeteorologyTimeSeries'[ForecastTime].[Date] ); MONTH ( tenDaysAfter ); DAY ( tenDaysAfter ) ) ) )Regards
7 Replies
Hi aristen,
My source is a single table containing 3 different time series.
Time series 1 (T1) goes from 01-01-2017 to (now+15 days).
Time series 2 (T2) goes from 01-01-1940 to 01-01-2015.
Time series 3 (T3) goes from 01-01-1965 to 01-01-2016
So dates from 01-01-1965 to 01-01-2015 are belong to both series 2 (T2) and series 3 (T3)?
My problem is T3. I don’t know how to expand the date range for that time series.
Based on my understanding, for T3, you can try getting the expanded range for the current selected range first, then you should be able to expand the date for T3 as what have done for T2 with the new expanded range.:smileyhappy:
Expand Date for T3 = VAR minDateSelected = MIN ( 'Date'[Date] ) VAR maxDateSelected = MAX ( 'Date'[Date] ) VAR tenDaysPrevious = minDateSelected - 10 VAR tenDaysAfter = maxDateSelected + 10 RETURN FILTER ( 'Date', 'Date'[Series] = "T3" && ( 'Date'[Date] >= DATE ( YEAR ( 'Date'[Date] ), MONTH ( tenDaysPrevious ), DAY ( tenDaysPrevious ) ) && 'Date'[Date] <= DATE ( YEAR ( 'Date'[Date] ), MONTH ( tenDaysAfter ), DAY ( tenDaysAfter ) ) ) )Regards
Hi v-ljerr-msft
Thank you for your answer.
I am not quite sure what your code does. Does it return a new table with the filtered rows?
I tried to fiddle around with it a bit, but i get this error:
'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value'.
Do you know why this error happens?
thanks!
Hi aristen,
I am not quite sure what your code does. Does it return a new table with the filtered rows?
Yes, it will return a new table with the filtered rows, I write it for your reference, and you should use it as part of your filters in your measure.
In addition, the formula is untested, as I don't have your real table structures for test. So could you post your table structures with some sample data and your expected result, if the formula doesn't work in your scenario? And it's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.:smileyhappy:
Regards
