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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Alpesh
Frequent Visitor

Different results for custom column in service

Hi, any help/thoughts, as I;m getting different results between Deskptop and Service. The service doesn;t show correct calculation...

 

Desktop (correct calculation)

Alpesh_1-1699896006370.png

 

Service (wrong calcultion):

Alpesh_0-1699895964421.png

 

Formula:

_1st Accepted Business Hours =
VAR _Start = 'Chat Table Unique'[Unprocessed Time]
VAR _End = 'Chat Table Unique'[Accepted Time]
VAR _Workhours =
SUMX(CALCULATETABLE(
    'Date - Working Hours per Day',
    DATESBETWEEN('Date - Working Hours per Day'[Date],_Start,_End),
    'Date - Working Hours per Day'[Final Working Day factoring in Holiday] = 1
    ),
    MAX(MIN('Date - Working Hours per Day'[Business End],_End) - MAX('Date - Working Hours per Day'[Business Start],_Start),0) *24
)
Return
IF(_Workhours,_Workhours,0)
1 ACCEPTED SOLUTION
Alpesh
Frequent Visitor

After alot of fault finding, it turns out that because I was converting in Power query every datetime to the local call center country it completely threw off the calculation when refreshed. So instead I used a DAX formula (TIME()) instead to convert the UTC times into the local timezones for the calculation... and now it works!!

View solution in original post

4 REPLIES 4
Alpesh
Frequent Visitor

After alot of fault finding, it turns out that because I was converting in Power query every datetime to the local call center country it completely threw off the calculation when refreshed. So instead I used a DAX formula (TIME()) instead to convert the UTC times into the local timezones for the calculation... and now it works!!

TheoC
Community Champion
Community Champion

@Alpesh make sure to mark your answer as the solution so others can find this post 🙂

 

All the best!

 

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Community Champion
Community Champion

@Alpesh  Ahhh yes, that will definitely influence the outputs. Well done on finding the cause!  This post is a great post to showcase the importance of applying a systematic process for data preparation (i.e. preparation / transformation / manipulation). 

Again, great work in finding the cause! This highlights something very important which is often overlooked:  when using Power Query to manipulate / transform data, we will not be able to easily see the log of these changes directly in Canvas view.  

 

Great stuff @Alpesh!

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Community Champion
Community Champion

Hi @Alpesh 

 

The DAX measure looks fine and is quite logical. I don't believe that's the issue.  From my experience, there are several reasons why this could be occuring:

 

1. Time Zone differences between your computer and the browser.

2. Filters applied / not applied in Power BI Service vs Desktop.

3. Row level security (if applicable) depending on how it's set up.

 

There are potentially other reasons but hopefully one of the above will help resolve.

 

Let me know how you go.

 

Theo 🙂

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.