Forum Discussion

njsimmons82's avatar
njsimmons82
New Member
1 month ago
Solved

Date Issue

Each time I use the created Date or Resolved Date in a new column or measure formula I receive an error that says "A single value for column "Created Date' in table x cannot be determined...."  I am ...
Need Help
power bi
  • ryan_mayu's avatar
    ryan_mayu
    1 month ago

    njsimmons82​ 

    The error occurs because you’re creating a measure, but [Created Date] and [Resolved Date] contain multiple rows. DAX needs an aggregation or a single selected value.

    maybe you can try this

    Resolution Days =

    VAR CreatedDate =

    SELECTEDVALUE('June 1 to Aug 19'[Created Date])

    VAR ResolvedDate =

    SELECTEDVALUE('June 1 to Aug 19'[Resolved Date])

    RETURN

    IF(

    NOT ISBLANK(CreatedDate) &&

    NOT ISBLANK(ResolvedDate),

    DATEDIFF(CreatedDate, ResolvedDate, DAY)

    )

     

    if this does not work, pls provide your sample data and expected output.

krishnakanth240's avatar
krishnakanth240
Icon for Super User rankSuper User
1 month ago

Hi njsimmons82​ 

If you want Resolution Days per ticket as row by row then create Calculated Column instead Measure

Resolution Days =

DATEDIFF(

'June 1 to Aug 19'[Created Date],

'June 1 to Aug 19'[Resolved Date],

DAY)

In calculated column DAX evaluates it row by row, so there is an implicit row context and each row's own Created or Resolved Date is used with no error

If you want a measure then you need to use column in an aggregation as measure has to view many rows into one number

Avg Resolution Days =

AVERAGEX(

'June 1 to Aug 19',

DATEDIFF(

'June 1 to Aug 19'[Created Date],

'June 1 to Aug 19'[Resolved Date],

DAY))

AVERAGEX or SUMX iterates row by row internally computing DATEDIFF for each row and aggregates the results