Cant measure from date column

2
votes

I'm trying to count days between a date from the column 'completionDate' and today

The table name is 'Incidents (2)'

I have a simuler table called 'incidents' here it's working.

The code: DaysClosed = DATEDIFF('Incidents (2)'[completionDate].[Dag];TODAY();DAY)

The error i get: 'A single value for variaton 'Dag' for column 'completionDate' in table 'Incidents (2)' 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.'

2
powerbidax
Can you plase give some more context on your (relevant) raw data and what do you want as outcome? Seems to me you want an extra column, not a full table.. – Aldert

2 Answers

1
votes

'Incidents (2)'[completionDate].[Dag] referencing a colomn. It is in your computation returning a table (multiple date in the evaluation context) instade of a scalar needed in DATEDIFF calculation.

You need to leverage to be sure that 'Incidents (2)'[completionDate].[Dag] is rturning a scalar value. To do that you can leverage rowcontext and then also formula like Max.

2
votes

The error you get strongly depends on how you are evaluating your formula, that's why it might work on another table but not on this one. As @JBfreefolks pointed out correctly you are specifying a column where a scalar value is expected. That can work depending on the context you are evaluating your formula over (assuming it is a measure).

For instance, imagine a data-set with 100 rows equally divided into four categories A,B,C,D. When you create a table visual with a row for each category, each row will have 25 underlying records that will be used in any calculation added to this row (either a measure or an aggregate of any value). This means that when using a formula like datediff with a column reference, it will get 25 values for it's second argument where it expects one.

There are several ways to solve the problem depending on your desired result.

  1. Use a measure like MAX like @JBfreefolks suggested to make sure that a single value is selected from multiple values. The measure will still be calculated over a group of records but will summarize it by taking the maximum date.

  2. Make sure the visual you are using has something like an ID in there so it doesn't group, it displays row context. Any measure added to this visual will evaluate in row context as well.

  3. Use a calculated column instead. They are always evaluated in row context first and their values can be aggregated in a visual later on. When using TODAY() , you probably need to refresh your report at least every day to keep the column up to date.

  4. A more complicated way is to use an iterator like SUMX() or AVERAGEX() to force row context evaluation inside of a measure.

Good to see you solved it already, still posting as it might be helpful to others.