Formula stops working when published

Power BI
1

Hi!

I’ve got an issue that I’ve never seen before and can’t quite figure out.

My formula works fine when working on the Power BI Desktop. However, it defaults to the alternate result in the divide formula when I publish it.

Can formulas max out cloud processing power? Could the issue be something else?

I can supply a model if it’s necessary. But, I’m not having issues with the syntax or the result (at least as far as I know).

Here is the formula:

Capacity Utilization % = DIVIDE( [Total Hours for CU], [Avaliable Hours], 1 )

Here are the supporting formulas

  1. Total Hours for CU = CALCULATE( [Total Hours], ALLSELECTED( Items[Item SubGroup] ) )
    a. Total Hours = SUM( Production[Total Hours] )

  2. Avaliable Hours = [Count of Line] * [Days Worked] * [Daily Capacity]
    a. Count of Line = CALCULATE( DISTINCTCOUNT( ‘Plant & Line’[Line] ), ALLSELECTED( ‘Plant & Line’[Line] ) )
    b. Days Worked = [Count of Days] * DIVIDE( [Working Days], 7, 0 )
    i. Count of Days = CALCULATE( DISTINCTCOUNT( ‘Date’[Date] ), ALLSELECTED( ‘Date’[Date] ), FILTER( ‘Date’, ‘Date’[Holiday] = “False” ) )
    ii. Working Days = SUMX( ‘Plant & Line’, ‘Plant & Line’[Working Days] / COUNT( ‘Plant & Line’[Line] ) )
    c. Daily Capacity = SUMX( ‘Plant & Line’, ‘Plant & Line’[Daily Capacity] / COUNT( ‘Plant & Line’[Line] ) )

2

Hi Wesley,

Does the published version have any filters that were somehow applied? Have you tried hitting the button for “Restore Defaults”?

The fact that it’s delivering the alternate result means that something is preventing the denominator from calculating itself properly. Can you verify that all your measures are delivering the correct answer outside of the DIVIDE formula?

3

Those are very good questions!

No.

Yes. It doesn’t fix the issue.

That’s a good question, and one I should have considered. I tested it and found that it worked initially (which has been consistent) and then stopped working in a few moments.

The problem is in the Count of Days formula because BI is converting the holiday on the date column from a true/false to a 0 or -1.

Have you ever seen this? If so, do you know how to fix it?

I’m about to step out of the office for the day, so I’ll investigate this further tomorrow.

4

Haven’t seen it but can do a bit of digging. In PQ editor, what data type is the Holiday column? Could it be something as simple as changing the type to Text so that it doesn’t convert the Boolean result (true/false) to a number?

2 Likes
5

This is exactly what I did and it solved the issue.

The holiday column was a true/false type in the Query Editor, but a text type in the BI Dashboard. I believe these two types were conflicting and the Query Editors type won the type mismatch in the cloud.

This was a weird issue! Thanks for helping me think through it :slight_smile:

1 Like
6

To the contributors of this post, thank you all for your inputs on this topic. We are now tagging it as SOLVED. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks!