Showing results for 
Search instead for 
Did you mean: 
Not applicable

Measure to count tasks left at each time step


I'm looking for help with a DAX expression that is supposed to return a value that is responsive to the date context. 

My dataset is a list of tasks with a due date. The end goal is to plot over time the number of tasks remaining, based on the due date (I don't consider the actual fulfillment here, this is just a planning tool to see ahead 🙂 ... ). Today, there may be 50 tasks, in a week maybe 40, and we will get to 0 tasks in, say, 2 months. I only look at future due dates. 

A Date dimension table is connected to the factTable[DueDate]. I am thinking of using this datedim for the x-axis of the graph, and the measure for the Values. 


I have a challenge with comparing the due date with each time step. I have tried the following measure: 

Tasks left = CALCULATE(COUNTROWS('All tasks'); 'All tasks'[DueDate] >= DimDate[Date])


 ... but I get the error message that :

A single value for column ‘Date’ in table ‘DimDate’ 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.


.. but in this case I don't want to give the min or max value of the dates: I want it to take any date that I feed it, and give me an answer! ...

I would also like to avoid Calculated Columns because I need it to be responsive to different filters and contexts.


Any thoughts are appreciated on this dark friday night... thanks community!!



Community Champion
Community Champion

Hi @Anonymous 


In your situation, you will need to wrap DimDate[Date] with aggregate function like MIN, MAX or SELECTEDVALUE as you are using >= operators and technically you could select two or more dates on the slicer, therefore, the engine needs to know which one to consider.


Hope this makes sense.


Best Regards,

If this post helps, then please consider Accepting it as the solution.



Not applicable

Hi @Mariusz , 

Thank you for your reply. It makes sense, but it seems it only works without context... a new mystery!! Hopefully you can help me 🙂

Say I have the following data:





It means there are in total, today, 8 tasks left, distributed differently across responsibles (A has 3, B has 1, C has 3 and D has 1).

If I use the following measure: 

Tasks left = CALCULATE(COUNTROWS('All tasks'); FILTER(ALL('All tasks'); 'All tasks'[DueDate] >= MIN(DimDate[Date])))
I get the correct burndown over time for the total amount, but I get the exact same values when responsible is included as context!
I am using a simple line chart over time (Date as Axis), with Responsible as Legend and the measure as Value. 
Best regards,

Hi @Anonymous 


Not sure if I understand your requirement, but have a look at the attached file and let me know if it's working for you.


Best Regards,

If this post helps, then please consider Accepting it as the solution.

Not applicable

Hi @Mariusz ,


Thank you so much for your reply. I see that your model is working for my purpose, great!! I would like to better understand how you got there: 

- Most importantly: I see there is no relationship between the fact table and the date dim table. If I create a relationship, the chart does not work anymore. Why is that? 

- In your measure 'Tasks Left', why do you use "FILTER(ALL('All tasks'[DueDate]); ..." ? I tried to replace this part with the table only ("FILTER('All tasks'; ... "), and it still works.


Thanks in advance!



Hi @Anonymous 


1. When you create a relationship you are forcing 'All tasks'[DueDate] = DimDate[Date] therefore >= operator will not work anymore.

2. Both will work fine 'All tasks'[DueDate] and 'All tasks' will work in your scenario, however, it's easier on the engine to iterate a column of unique values vs table having said that, you should not see any performance difference on smaller datasets.



Best Regards,

If this post helps, then please consider Accepting it as the solution.


Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors