Forum Discussion

aristen's avatar
aristen
Frequent Visitor
9 years ago
Solved

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 t...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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

  • aristen's avatar
    aristen
    Frequent Visitor
    9 years ago

    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!

     

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      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

      • aristen's avatar
        aristen
        Frequent Visitor
        9 years ago

        Hi again v-ljerr-msft. First off, thanks for your help!

        Below you can see my current setup.

        Dimmdate:

        Contains all possible dates, the user uses ‘DateToFilterOn’ as daterange filter.

         

        BaseDate:

        Contains 366 rows with unique MonthAndDate.

         

        Timeseries:

        My datatable.

        DatasubTypeName is timeseries identifier (T1,t2,t3)

        ForecastTime: timestamp

        Date: Date part only of ForecastTime.

         

         

        The user selects a range for ‘DateToFilterOn’ which maps to MonthAndDay.

         

        This insures that I get all data i need, except for T3 where I need to get +/- 10 days.

         

        Does this make sense ?