Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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())
)
)
)
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())
)
)
)
@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
)
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())
)
)
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |