Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a dax query from an SSAS cube. This takes an average of the time of day from a time dimension. I have tried a couple of measures but I am getting terrible performance trying to convert the average which is a decimal number to HH:mm:ss using format? I have this query below for an SSRS report. the Measures in the cube are decimal for average time of day 0 to 1. The query returns in under a second but if I add the bottom 2 measures that format the time as HH:mm:ss it never finishes? Is this a limitation of dax? I can create the time format in SSRS but I thought I could do this in dax.
DEFINE
VAR _Company=3222
VAR _StartDate = date( 2022, 08, 20 )
VAR _EndDate = date(2022,09,01)
EVALUATE
SUMMARIZECOLUMNS (
Engineers[Person Id],
Engineers[Engineer],
Engineers[Group Id],
Engineers[Group Description],
'Time'[AM PM],
'Date'[Start Date of Week],
'Date'[Week of Year],
'Date'[Month Start Date],
'Door Event Type'[Door Event Type Description],
'Door Oversize'[OverSizeDescription],
FILTER (
ALL ( Exchanges[DC Id] ),
CONTAINSROW (
{ 65, 66, 95, 97, 93, 92, 94, 85, 89, 91, 191, 321, 90, 96 },
Exchanges[DC Id]
)
),
FILTER ( ALL ( Company[Company Id] ), Company[Company Id] = _Company ),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] >= _StartDate// DATE ( 2022, 08, 01 )
&& 'Date'[Date] < _EndDate // DATE ( 2022, 09, 01 )
),
"Average Dwell Time", [Average Dwell Time],
"Door Events", [Door Events],
"Earliest Event", [Earliest Event],
"Latest Event",[Latest Event],
"Earliest Event format", format( [Earliest Event],,"HH:mm:ss")
"Latest Event format",format([Latest Event],"HH:mm:ss")
)
Solved! Go to Solution.
@gooranga1 See if this helps: Chelsie Eiden's Duration - Microsoft Power BI Community
@gooranga1 See if this helps: Chelsie Eiden's Duration - Microsoft Power BI Community
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |