Reference a column in a measure using DAX

[micheledubose]

Hello,

I am a DAX newbie and trying to write an expression like the following SAP formula.  The objective here is to convert a number like 319 to 5:19.  5:19 represents a duration in HH:MM:SS.  The issue that I'm experiencing with creating the below formula in DAX is that my table and column do not display when referenced with an IF or TRUNC function.  What expression or function can I use in DAX similar to the formula below?

If([Duration]>59;Concatenation(Concatenation(Truncate([Duration]/60;0);":");Mod([Duration];60));[Duration])

BTW, in the above formula, Mod returns the remainder from the division of two numbers.

[micheledubose · Accepted solution]

I've created the DAX formula in my tabular model in a calculated column which was a workaround to my problem.  I understand in Power BI Desktop, you can create a calculated column but in my case, since I'm connecting to an SSAS data source, the calculated column is not available. I can only create measures in Power BI Desktop, in which, in order for a table to be referenced, you have to use an aggregate function.  Unfortunately, I cannot use an aggregate function for a column that's a string.  Well, at least, I don't think I can.  Nevertheless, as mentioned, creating the calculated column in my tabular model worked.

[wdx223_Daniel]

@micheledubose try =FORMAT([Duration]/86400,"hh:mm:ss")

[micheledubose]

@wdx223_Daniel 

Even with FORMAT, my table column does not display.  My data source is SQL Server Analysis Services Database in DirectQuery mode so that may make a difference.

EDIT:

The DAX formula works in Visual Studio tabular model project but not in Power BI Desktop.  I would prefer to create a measure in Power BI Desktop instead of a calculated column in the tabular model.  Why isn't the tables recgonized in a measure in Power BI Desktop?