Help with date filters

Question

Hi, I'm working with different filters through measurements using dates, and I'm constantly getting the following error that I can't fix: "The expression refers to multiple columns. You cannot convert multiple columns to a scalar value."

How can it be solved? Can this have to do with working with a date column?

I leave an example of one of the measures I am working on:

FechaEvaluada = FILTER('Tasks', DATESBETWEEN('Tasks'[TaskDate], [FechaDesde], [FechaHasta]))

Then, is there any way to apply this type of filters directly on a graph? For example, having a stacked bar chart filtered by measurement deadlines. Thank you!!

syndicate_admin · Answer

Again thank you for your answer, the problem I mentioned in my previous comment I managed to fix. Anyway, I still can't filter my chart as I need, I'll try to explain in detail the filtering and share screenshot:The chart must be filtered under two conditions: From the date indicated in "FromDate" to the last day of the month indicated in the month/year filter.The "FromDate" is defined as the oldest date where there is a task with an "Overdue" status (you can see the status of the tasks in the chart legend).In the example in the image, the graph should then show every month from July 2021 to October 2022. Thank you!!

amitchandak · Answer

Syndicate_Admin , 
The meausre would be like
&nbsp;
countrows(FILTER('Tasks', DATESBETWEEN('Tasks'[TaskDate], [FechaDesde], [FechaHasta])))
&nbsp;
You can use measure in visual level filter

anonymous · Answer

Hi&nbsp;Syndicate_Admin&nbsp;,
&nbsp;
As far as I know,&nbsp;DATESBETWEEN() will return a column instead a scalar value. However, measure need to be a aggregation value, so your measure will return error.
&nbsp;
For reference:&nbsp;DATESBETWEEN
Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date.
This function is suited to pass as a filter to the&nbsp;CALCULATE&nbsp;function. Use it to filter an expression by a custom date range.
&nbsp;
According to your statement, I think you want to return all taskdates in date range. Here I suggest you to create a table visual with taskdates, then create a measure as a visual level filter.
Filter = 
IF (
    MAX ( Tasks[TaskDate] )
        IN DATESBETWEEN ( Tasks[TaskDate], [FechaDesde], [FechaHasta] ),
    1,
    0
)
In my sample,&nbsp; FechaDesde = 2022/10/21 and FechaHasta = 2022/11/01, TaskDate is whole 2022. Add this measure as a visual level filter and set it to show items when value = 1.

&nbsp;
Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
&nbsp;

syndicate_admin · Answer

Thank you very much for your answer, I think this solution could work, I am trying to apply it but I am having a problem for having duplicate task dates, it tells me that this is not allowed:

Is there a way to fix it?

syndicate_admin · Answer

Hi, thanks for your answer, I was trying to apply it to my table and graph but I get the following error that I can't fix: "A multi-valued table was provided where a single value was expected."
How could I fix it?
I am using a calendar table to be able to filter my data, since the original table had duplicate dates and this was also causing me problems. Thank you!

Forum Discussion

Help with date filters

6 Replies

Related content

Filter

Dynamic Filter

Override filters

Inverse Filter

Filter on Icons ?