Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATADIFF as measure

Good day,   the following formula works fine as a calculated column, but not as a measure. When do I have to change if I wanted to have this calculation done by a measure? Runtime = DATEDIFF([St...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , you can create as the measure then you may have to take min/max on each side and force a row context.

    like

    sumx(Table,DATEDIFF(Table[StartedAt];Table[StoppedAt];SECOND))
    avergageX(Table,DATEDIFF(Table[StartedAt];Table[StoppedAt];SECOND))

    avergageX(values(Table[ID]),DATEDIFF(Table[StartedAt];Table[StoppedAt];SECOND))

    or

    Like

    sumx(summarize(Table,Table[ID],"_1",DATEDIFF(min(Table[StartedAt]);max(Table[StoppedAt];SECOND))),[_1])

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
6 years ago

HI Anonymous ,

 

I have used DATEDIFF in measures before. Seeing your formular below:

Runtime = DATEDIFF([StartedAt];[StoppedAt];SECOND)

 

Looks like the highlighted ones are measures. In order to create a measure using DATEDIFF, you need to replace these highlighted measure with some datetime columns along with some aggregation functions - MAX, MIN

Refer screenshot for example as below:

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello Pragati,

     

    I should have been more detailed. No, those two fields are not measures. I have imported the data from a normal Excel file and those two fields are regular columns from the table.

     

    I started on PowerBI only yesterday. I usually work on BOXI and SSRS, but now we are to have a look into PowerBI. So I tought that a measure is a normal, calculated variable. It seems to me that measures are more like aggregating variables, hence the need to use functions such as min/max. I appreciate your help, though!