topic Re: Filter Visuals on multiple date columns in a table using single date SELECTEDVALUE filter in DAX Commands and Tips

Filter Visuals on multiple date columns in a table using single date SELECTEDVALUE filter

aghanchi — Wed, 16 Jul 2025 19:07:42 GMT

I work for a nonprofit where we have to report case counts among other things. One of the reports asks for cases that are in open state at a certain date. The fact table has an OpenDate and CloseDate column. Null or empty CloseDate indicates that the case is still open. I also have a Dates table that I use for other calculations. I have set relationship between the fact table and the Dates table based on the OpenDate and CloseDate columns to the Dates[Date] column, but none of them is active.

I want to add visuals like table and card that would show the number of cases that are open on a certain date. I can specify that date as a Page filter or Visual filter using the Dates[Date] column .

I have already tried solutions like adding a boolean column to the fact table that would return True/False based on a formula that uses the OpenDate, CloseDate and the SelectedValue(Dates[Date], Today()). But that is giving me erroneous results. I have read that Measures is the way to do it. I have created a measure that returns 1 and 0 (for True/False), But the edit time errors indicate that I have to use one of the aggregate functions because my fact table is not returning single value. Following is what I have done so far. This is a simplified version. I also have used USERELATIONSHIP() between the OpenDate/CloseDate columns and the SelectedValue(Dates[Date], Today()) function to no avail. Can you please help me how do I resolve this problem?

Thank you in advance.

Measure_ISOpenAsOfSelectedDate = if( AND( AllSRs[OpenDate] <= SELECTEDVALUE(Dates[Date], TODAY()) , or( ISBLANK(AllSRs[CloseDate]), AllSRs[CloseDate] > SELECTEDVALUE(Dates[Date], TODAY()) ) ) ,1 ,0 )

Error: A single value for column 'OpenDate' in table 'AllSRs' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Re: Filter Visuals on multiple date columns in a table using single date SELECTEDVALUE filter

Greg_Deckler — Wed, 16 Jul 2025 19:37:06 GMT

aghanchi Try:

Measure_ISOpenAsOfSelectedDate = if( AND( MAX( AllSRs[OpenDate] ) <= SELECTEDVALUE(Dates[Date], TODAY()) , or( ISBLANK(AllSRs[CloseDate]), AllSRs[CloseDate] > SELECTEDVALUE(Dates[Date], TODAY()) ) ) ,1 ,0 )

Re: Filter Visuals on multiple date columns in a table using single date SELECTEDVALUE filter

aghanchi — Wed, 16 Jul 2025 20:15:02 GMT

Greg, thank you for your response!

Unfortunately, that didn't work. I had to apply the MAX function to other instances of OpenDate and CloseDate columns in the query to bypass the same error. In the end, when I applied tat measure as the visual filter, nothing happened. It did not return 1 or 0 either.

Anyways, I was able to lookup the following measure that at least gets the totals right for the Card visual. I still want to resolve it for the table visual where I am listing the actual cases.

Measure_OpenAsOfSelectedDate = Calculate( DISTINCTCOUNT(AllSRs[ServiceReqID]), AND( AllSRs[OpenDate] <= SELECTEDVALUE(Dates[Date], TODAY()) , or( ISBLANK(AllSRs[CloseDate]), AllSRs[CloseDate] > SELECTEDVALUE(Dates[Date], TODAY()) ) ) )

Re: Filter Visuals on multiple date columns in a table using single date SELECTEDVALUE filter

aghanchi — Wed, 16 Jul 2025 23:52:30 GMT

I resolved it by adding the same Measure (shown below) that I used for the card visual into a table visual alongside with other fields that I needed for my case list report and it worked! I just now need to hide that measure column from the table because it is showing a "1" in every row 🙂