This can be done by searching a substring that states that the measure represent a duration.
#Power bi matrix tabular format code#
To make the CG only format the duration type measures we must change the code by adding a check on the selected measure name. When we add this measure to the matrix it gets formatted as if it were a duration. For instance, let’s add a measure that calculates a cost, like for instance 1$ per hour Cost = * 24 * 1
Our new toy is now applied to whatever measure happens to be used in our report. Wonderful! So everything works fine and we can now use use our Duration Formatter every time a duration requires to be formatted. It’s important to know that when selecting two calculation items from the slicer, the CG behaves like when no selection is present. Now we can chose the desired format using the slicer and we can also read the name of the measure instead of the Calculation Item as the column header.
So we can remove the CG from the matrix column and create a Slicer instead. We are most likely going to choose the format to be used with a slicer. Of course we are not going to use this CG by replicating the same measure with different formats as columns in a matrix. But resetting the Duration column data type to “Decimal number” solves the issue. There is a left alignment problem due to the Time data type I set on the Duration column for the first example.
#Power bi matrix tabular format full#
Now the matrix with the full CG looks like this It works! We can now add new Calculation items, to also format the duration as a decimal number representing, for instance, the number of days, or the number of hours, and so on. To check that the calculation group is working correctly, we can implement a measure Sum Duration = SUM(T)Īnd then use it in a matrix, with the calculation group on the columns The calculation group can now be seen in Power BI as a table in the data view Since Tabular Editor does not synchronize automatically the model to Power BI, we need to save the changes to Power BI by clicking on the Save button in Tabular Editorįinally we must click on the Refresh Now button that appears in Power BI I named it “DDDHHMMSS” and then I wrote the same DAX code as before, just replacing the column reference with the SELECTEDMEASURE() function, and moving the custom format expression to the Format String Expression property VAR D = SELECTEDMEASURE()ĭD * 1000000 + HH * 10000 + MM * 100 + SS Then using the context menu over the newly created Calculation Group we can create a new Calculation Item Then using the context menu over the Tables folder we create a new Calculation GroupĪnd we give it a meaningful name.
This is where the calculation groups come into play: if we implement a calculation group to do the formatting, we can write it once and use it in combination with any existing measure.Ĭreating a Calculation Group in Power BI requires Tabular Editor, that can be launched from the External Tools ribbon in Power BI (yes, I have 2 icons of TE in my ribbon: usually only one is installed) This works, but this code has to be replicated per each column or measure representing a duration. The following is a straightforward implementation for a calculated column: Formatted Duration = But writing some code is required in order to build the 9 digit number. This format string displays a 9 digit number adding the colon as a separator between groups. The first option is to use the FORMAT function with a custom format string, like for instance “000:00:00:00”.
Therefore, in order to display a duration longer than one day, we have to write some DAX code. We can see it by entering a duration of 1.5 days, that is shown as 12:00:00, that’s 12 hours instead of 36. The problem with longer durations is that when it is more than one day, the days are lost and only the fractional part of the day is shown. This displays the duration in hours, minutes and seconds as hh:nn:ss (it’s “nn”, since the format “mm” is used for the month) When the duration is less than one day, an easy solution is to set the column Data Type in the Data View to Time, and then select the Format (hh:nn:ss) DAX has no duration type, nor a built-in format string to display a duration as days, hours, minutes and seconds.
0 kommentar(er)
