Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to 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.
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.
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?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |