Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Converting Tableau Calculation to Power BI

Hi

I have this column in a tableau dashboard that I built and I was wondering how I convert the data in the same way. I am trying it through the custom column in Power Bi. My calculation in Tableau was

STR(INT(SUM([Total Display Time])/86400))

+ " Days, " +

IF (INT(SUM([Total Display Time])%86400/3600))

< 10 THEN "0" ELSE "" END + STR(INT(SUM([Total Display Time])%86400/3600))

+ " Hours, " +

IF INT(SUM([Total Display Time])%3600/60)

< 10 THEN "0" ELSE "" END + STR(INT(SUM([Total Display Time])%3600/60))

+ " Minutes, " +

IF INT(SUM([Total Display Time]) %3600 %60)

< 10 THEN "0" ELSE "" END + STR(INT(SUM([Total Display Time]) %3600 %60))

+ " Seconds "

 

Could someone possibly help me convert this to a Power BI to end with the same results?

 

TIA

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Anonymous 

     

    Try create this using dax with calculate column:

     

    CONCATENATE(SUM([Total Display Time])/86400,
    CONCATENATE("Days"
    CONCATENATE(IF(SUM([Total Display Time])/86400/3600<10,"0",""),
    CONCATENATE("Hours",
    CONCATENATE(IF(SUM([Total Display Time])/3600/60<10,"0",""),
    CONCATENATE("Minutes",
    CONCATENATE(IF(SUM([Total Display Time])/3600/60<10,"0",""),
    CONCATENATE(SUM([Total Display Time]) /3600/60), "Seconds"))))))))
     
     
    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , In power query

     

    Duration(Number.IntegerDivide(Number.FromText([Cycle:duration]),86400) ,Number.IntegerDivide(Number.Mod(Number.FromText([Cycle:duration]),86400),3600),
    Number.IntegerDivide(Number.Mod(Number.Mod(Number.FromText([Cycle:duration]),86400),3600),60),
    Number.Mod(Number.Mod(Number.Mod(Number.FromText([Cycle:duration]),86400),3600),60)
    )

     

    DAX  - Measure or column both will work
    Quotient([Total Display Time],86400) & ":" Quotient(mod([Total Display Time],86400),3600) &":" & Quotient(mod(mod([Total Display Time],86400),3600),60) &":" & mod(mod(mod([Total Display Time],86400),3600),60)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Unfortunately this didn't work in my custom column am I doing something wrong?

       

      Thanks

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous 

     

    Try create this using dax with calculate column:

     

    CONCATENATE(SUM([Total Display Time])/86400,
    CONCATENATE("Days"
    CONCATENATE(IF(SUM([Total Display Time])/86400/3600<10,"0",""),
    CONCATENATE("Hours",
    CONCATENATE(IF(SUM([Total Display Time])/3600/60<10,"0",""),
    CONCATENATE("Minutes",
    CONCATENATE(IF(SUM([Total Display Time])/3600/60<10,"0",""),
    CONCATENATE(SUM([Total Display Time]) /3600/60), "Seconds"))))))))
     
     
    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.
  • SahilKumar's avatar
    SahilKumar
    Frequent Visitor
    1 year ago

    Before jumping into the solution, it would help to clarify how you’re planning to analyze or visualize this data. For example, the LOD (Level of Detail) logic you used in Tableau—was it applied as a calculated field or aggregated measure? Similarly, in Power BI, whether you need a measure or a calculated column will influence how the solution is implemented.

     

    Here’s the DAX equivalent that mimics your Tableau formatting:

     

    Formatted Display Time :=
    VAR TotalSeconds = SUM('YourTable'[Total Display Time])
    VAR Days = INT(TotalSeconds / 86400)
    VAR Hours = INT(MOD(TotalSeconds, 86400) / 3600)
    VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
    VAR Seconds = MOD(TotalSeconds, 60)

    VAR FormattedHours = IF(Hours < 10, "0" & Hours, FORMAT(Hours, "0"))
    VAR FormattedMinutes = IF(Minutes < 10, "0" & Minutes, FORMAT(Minutes, "0"))
    VAR FormattedSeconds = IF(Seconds < 10, "0" & Seconds, FORMAT(Seconds, "0"))

    RETURN
    FORMAT(Days, "0") & " Days, " &
    FormattedHours & " Hours, " &
    FormattedMinutes & " Minutes, " &
    FormattedSeconds & " Seconds"

     

    Did I answer your question? Mark my post as a solution!

     

    Also, if you're frequently working with DAX, I'd highly recommend using a tool like DAX Optimizer Gen AI.