Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aristen
Frequent Visitor

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 😊

1 ACCEPTED SOLUTION

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 ). Smiley Happy

 

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 ) )
        )
    )

mm.PNGfilter.PNG

Regards

View solution in original post

7 REPLIES 7
v-ljerr-msft
Employee
Employee

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.Smiley Happy

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.Smiley Happy

 

Regards

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

Below you can see my current setup.

powerbi setup.PNG

powerbi setup2.PNG

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 ?

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 ?

 

 

 

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 ). Smiley Happy

 

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 ) )
        )
    )

mm.PNGfilter.PNG

Regards

thanks @v-ljerr-msft, that solved my problem 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.