Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
gooranga1
Power Participant
Power Participant

Using format to convert decimal to HH:mm:ss really slow

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")
    
)

 

 

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@gooranga1 See if this helps: Chelsie Eiden's Duration - Microsoft Power BI Community


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
gooranga1
Power Participant
Power Participant

@Greg_Deckler  perfect! Thank you and @konstantinos !

Greg_Deckler
Super User
Super User

@gooranga1 See if this helps: Chelsie Eiden's Duration - Microsoft Power BI Community


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.