Measure to count tasks left at each time step

[Anonymous]

Hi,

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!!

[Mariusz]

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,
Mariusz

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

[Anonymous]

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:

TaskNr	DueDate	Responsible
1	13.12.2019	A
2	15.12.2019	D
3	18.12.2019	C
4	22.12.2019	C
5	22.12.2019	B
6	31.12.2019	A
7	31.12.2019	A
8	31.12.2019	C

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,

Ginevra

[Mariusz]

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,
Mariusz

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

[Anonymous]

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!

Ginevra

[Mariusz]

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,
Mariusz

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