A Single value for column in table cannot be determined

[qlnv]

Hi, I'm trying to create a column/measure in direct query mode but it doesn't work. (it works in imported mode). 

The error: 

A single value for column 'NGAY_HL' in table 'BI_XE_HOP_DONG' 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.

My dax: 

DELTA_NGAY = if(DATEDIFF('BI_XE_HOP_DONG'[NGAY_HL], TODAY(), DAY) > 0, IF(DATEDIFF('BI_XE_HOP_DONG'[NGAY_HL], TODAY(), DAY)>'BI_XE_HOP_DONG'[PERIOD], 'BI_XE_HOP_DONG'[PERIOD],DATEDIFF('BI_XE_HOP_DONG'[NGAY_HL], TODAY(), DAY)) ,0)

I want to create a column/measure check if NGAY_HL < TODAY and datediff of NGAY_HL and today > period then the result is period; Else, The result is DATEDIFF('BI_XE_HOP_DONG'[NGAY_HL], TODAY())

Thank you! 

[amitchandak · Accepted solution]

@qlnv , This should work as a column , In case of measure you need to use aggregation on column or use them inside x functions

DELTA_NGAY = sumx('BI_XE_HOP_DONG',  if(DATEDIFF('BI_XE_HOP_DONG'[NGAY_HL], TODAY(), DAY) > 0, IF(DATEDIFF('BI_XE_HOP_DONG'[NGAY_HL], TODAY(), DAY)>'BI_XE_HOP_DONG'[PERIOD], 'BI_XE_HOP_DONG'[PERIOD],DATEDIFF('BI_XE_HOP_DONG'[NGAY_HL], TODAY(), DAY)) ,0))