Forum Discussion
"A Single Value for Column Cannot Be Determined" Error Message when Add New Column
Hello all, it would be great if someone here can help me with this.
I have a simple table of 3 columns: Deal Close Date, Revenue Date, Valid. The Date columns are in date format and Valud column only contains "Yes" and "No".
Now I want to create a calculated column in the same table to find out (Revenue Date - Deal Close Date) with below conditions:
1. Revenue Date is not blank
2. Deal Close Date > 1 Jan 2018
3. ValId column = "Yes"
I have created a column with below DAX:
=CALCULATE( [Revenue Date]-[Deal Close Date],
NOT(ISBLANK([Revenue Date])),
[Deal Close Date] > DATE(2018,1,1),
[Valid] = "Yes")
However, I received the error message of "A Single Value for Column Cannot Be Determined". I removed all the conditions in th CALCULATE formula but the same error message still appears. However, if I build the column with just [Revenue Date]-[Deal Close Date], there is no error.
May I know why and how can I fix this? Many thanks!!
Hi superhayan
As you create a column and not the measure you need a formula on "cell level".
Try to use "if" :Deltadates = if(NOT ISBLANK('Table'[Revenue date]) && 'Table'[Deal close date]>date(2018,01,01) && 'Table'[Valid]="yes",DATEDIFF('Table'[Deal close date],'Table'[Revenue date],DAY),blank())pbix with example is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
2 Replies
Hi superhayan
As you create a column and not the measure you need a formula on "cell level".
Try to use "if" :Deltadates = if(NOT ISBLANK('Table'[Revenue date]) && 'Table'[Deal close date]>date(2018,01,01) && 'Table'[Valid]="yes",DATEDIFF('Table'[Deal close date],'Table'[Revenue date],DAY),blank())pbix with example is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
That works! I didn't reaslize that CALCULATE cannot be used on making calculated columns. Thank you!
