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

[aghanchi]

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.

[aghanchi · Accepted solution]

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 🙂

Measure_OpenAsOfSelectedDate = 

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

[Greg_Deckler]

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

[aghanchi]

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