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

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 ?

    • aristen's avatar
      aristen
      Frequent Visitor
      9 years ago

      Hey again v-ljerr-msft.

      I have changed the setup and managed to insert your snippet. I now only have DimDate and FactMeteorologyTimeSeries tables with no relationships.

      The user still filters the visual using DimDate[DateToFilterOn]

      I have created two measures in DimDate (min, max dates).

      Currently it is setup like below:

       

      table = 
VAR minDateSelected =
    DimDate[min]
VAR maxDateSelected =
    DimDate[max]
VAR tenDaysPrevious = minDateSelected - 10
VAR tenDaysAfter = maxDateSelected + 10
RETURN
    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 ) )
            
    )

      My problem with this is that DimDate[min] & DimDate[max] measurements are always the lowest and highest value of the raw values in DimDate and does not take into account the context of the user filtering.

      I also tried to just do the MIN(DimDate[DateToFilterOn] directly in the filtering which gave the same result

       

      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 ?

       

       

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        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