I have SQL Server databases that I am writing PowerBI reports for.
The fact tables in these databases can get a bit chonky (1,000,000,000+ rows), so I am using Analysis Services Tabular in the middle -- to allow me to define partitions with controlled refresh, mainly. The SQL Servers can not be hit directly by reports, they do need all of these rows, and reports can not be more than 15 minutes behind.
About 90% of the values I need to show are durations, which are defined in the base tables as whole seconds or milliseconds.
A lot of the time, these values need to be shown in hh:mm:ss format.
PowerBI doesn't have any support for a duration type (which is a massive, massive WTF if you ask me), but it is relatively easy to add a measure to a table containing a duration value in seconds and convert it into hh:mm:ss using DAX:
var duration = Sum/Average/Min/Max([SOMEFIELD])
var hours = Int(duration / 60)
var minutes = Int(Mod(duration - (hours * 60), 60) / 3600)
var seconds = RoundUp(Mod(Mod(duration - (hours * 60), 60), 3600), 0)
var h = If(hours > 9, "" & hours, Right("0" & hours, 2))
var m = Right("0" & minutes, 2)
var s = Right("0" & seconds, 2)
return If(IsBlank(duration), Blank(), h & ":" & m & ":" & s
This formula works fine when added as a measure in PowerBI.
But, we are talking 3-11 times per report, for 150+ reports. As some of you may have already noticed, some of that DAX is a bit janky and I will definitely want to revisit it at some point -- but not 800 times.
My question: how do make it so I don't have to maintain 800 similar copies of this code?
- As far as I know, there is no way to share DAX formulae.
- Saving the PowerBI model and reusing it saves the model, which as far as I can tell does not include locally added measures. Not when based on an SSAS Tabular Model.
- Since I am working off of a SSAS Tabular Model, I cannot actually add fields to the model.
- I can add a field to the SSAS Tabular Model and use that DAX formula, but that doesn't work -- it gets resolved on the SSAS Server and stomped flat into text. Which means you can calculate the field, and you can add it, but it doesn't know anything about the scope -- you just get the Sum/Average/Min/Max of all rows.
- Setting FormatString on the SSAS field to
hh:mm:ssdoesn't work, since it is not an actual time field, nor a calculable field.
If I do anything to it on the SSAS side, the field comes over as a string and is stomped flat, or completely devoid of context, is what I am saying.
Any thoughts?
