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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.