Running Measure filter

[Anonymous]

Hi,

Is it possible to filter the page to say only display when e.g. table1[End Date] is greater than the Parameter[start date]?

I was hoping this could be a running measure if thats possible?

Thanks

Liam

[amitchandak]

@Anonymous , date slicer you have an option for greater than there is a small icon on slicer to change type.

[Anonymous]

Hi @amitchandak ,

I dont think that would let me on the standard filter.

I only want it so the end date field is greater than e.g. below 01/01/2020 (first parameter), but it wont let me reference the measure there.

Is this possible?

[amitchandak]

@Anonymous , No, you are not allowed to have function, static you can have. 

This Min is based on some other parameter ??

[Anonymous]

you could add a custom column 

= Table.AddColumn(#"Renamed Columns", "Custom", each if [End Date] > [Start Date] then [End Date] else null) 

then filter out the null values. 

[Anonymous]

Hi @Anonymous ,

Apologies, it will be a parameter date so e.g. MIN(date[date]), which will be a moving date so I dont think this will work in a column form.

Thanks

Liam

[Fowmy]

@Anonymous 

Create Measure like MyParameterDate

Add new column in the calendar table in the model as: Calendar[Date] < MyParameterDate

Now place that New Column in the page filter and set to TRUE.

This will dynamically filter the calendar table based on the measure value.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Anonymous]

Hi @Fowmy ,

Ive created the column Filter = dimdate[date] < [ParameterStartDate] and every cell is FALSE.

The calendar goes up to 2028. Would this work with a column?

Thanks

[Fowmy]

@Anonymous 

Your [ParameterStartDate]  should be a measure and should not draw dates from the calendar as it will create a circular reference. Ii should be independent.

Please share what you have done as a sample file.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Anonymous]

Hi @Fowmy ,

Ive attached the file below. In the sample I would like values to be present only if the 'New End Date' is greater than the 'ParameterStartDate' measure.

I hope that makese sense. 

https://1drv.ms/u/s!AtcnGX-0tS5riVQegrm-rP9nx5dX?e=Ru8O5g

Thanks in advance

Liam

[Fowmy · Accepted solution]

@Anonymous 

Check the attached file. I created a relationship between Parameter Table and the Room Utilization Table. Added a New Column in Room utilization table to Flag more than MIN. The Report filters the new column with value TRUE.

https://1drv.ms/u/s!AmoScH5srsIYgYImqrSWw9X3ydaXuw?e=NDL21k

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Anonymous]

Hi @Fowmy ,

I have a measure that goes into the table along with new start date and new end date, and by creating this relationship it has changed the results.

Is there a way around this?

Thanks

[Fowmy]

@Anonymous 

As per your original question and the explanation you provided, my solution works, great!
Now it is a matter of configuring and adapting it to your model.

Maybe you can raise the new question in the forum to get your new question answered.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy 

Thankyou so much for explaining - that works perfectly.

One question, I have other measures running off of the Parameters, will the new relationship interfere at all?

Thanks

[Anonymous]

Hey Liam, 

See attached. I've added a simple date diff column 

Datediff = DATEDIFF(RoomUtilisation[New End Date],[ParameterStartDate],DAY)

then filtered out anything greater than 0 

https://1drv.ms/u/s!AnIEh6WhI4JogrAAnyDVVkj51F1Xdg?e=902oee

It's important to note because your ParameterStartDate is 2012 and your new End date only goes back to 2016, it's not actually filtered anything out. but it would do if you were to change the ParameterStartDate to something after 2016

[Anonymous]

Hi @Anonymous ,

Apologies if I haven't explained very well.

In the image below, as the Parameter start date, which has a measure containing MIN(Parameter[date]) is 14/09/2020, I wouldnt want to have anything in the table next to it with a New End date before the 14/09/2020, and as you can see theres quite a few.

The date slicer still just contains the date field, but I didnt know whether I could filter off of the first date in the slicer.

Maybe I would have to split the date parameters into two.

[amitchandak]

@Anonymous , can you give an example with data.