Forum Discussion
Quick Measure (time table) stops grouping customer
Need a little bit help.
I count (sum) different (this moment 3) time tables. The first 2 tables are working as intended. The table shows the customer (once) with the calculated time. When I add the 3th Measure the table shows each customer 13 times. The 3th column shows 13 times the same time but not correct. And the time is the same for every customer.
I use DAX to convert the seconds to readable hours and minutes. But even when I only count the seconds I have the same weird behavior.
Example without column TD_Operationeel (table as expected)
Example with column TD_Operationeel (first row in table above is now shown 13 times)
4 Replies
- amitchandak
Super User
jjwerkman ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi amitchandak thanks for the quick response. I'm not shure how to mask or delete sensitive data. I'm a newby and need to use Google a lot 🙂
I can export as table, but I'm not sure if that is wat you mean. A little bit more explanation: the customer name and the available hours (first column) are fetched from software A, the other 3 columns are fetched from software B.
The used DAX for TD_Beheer and TD_Operationeel is (same, but fetched from different tables):TD_Beheer =VAR Beheer = CALCULATE(SUM(IncidentTimeRegistrations_table[timeSpent_inSeconds]), FILTER(IncidentTypes, IncidentTypes[name] = "Beheer"))VAR Hours = INT ( Beheer / 3600)VAR Minutes = INT ( MOD( Beheer - ( Hours * 3600 ),3600 ) / 60)VAR Seconds = ROUNDUP(MOD ( MOD( Beheer - ( Hours * 3600 ),3600 ), 60 ),0)// Hours with leading zerosVAR H =IF ( LEN ( Hours ) = 1,CONCATENATE ( "0", Hours ),CONCATENATE ( "", Hours ))// Minutes with leading zerosVAR M =IF (LEN ( Minutes ) = 1,CONCATENATE ( "0", Minutes ),CONCATENATE ( "", Minutes ))// Seconds with leading zerosVAR S =IF (LEN ( Seconds ) = 1,CONCATENATE ( "0", Seconds ),CONCATENATE ( "", Seconds ))// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"RETURNIF (Beheer <> BLANK(),CONCATENATE ( H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S )))),BLANK())- amitchandak
jjwerkman , you can take some sample data and copy-paste here. Both input and output expected.
I think these blogs can also help you
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
