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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
aghanchi
Helper I
Helper I

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

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.

1 ACCEPTED SOLUTION
aghanchi
Helper I
Helper I

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

 

View solution in original post

3 REPLIES 3
aghanchi
Helper I
Helper I

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
Community Champion
Community Champion

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.