visual error displaying on service, no refresh error on service or desktop

[IncursioML1]

Hi All

There is something strange going on and not sure if a bug or if the Service does not have capability.

I have the situation with known information below

On latest version of PowerBI Desktop for Aug 2021

Dataset

1. SQL Server (On Premise)
   1. 3 direct queries
2. SharePoint Online List
   1. 2 Lists

PBI Gateway

1. Created on another physical machine to have both SQL Server credentials and SharePoint Online credentials
2. Using SSO for SQL Server and OAuth method on SP Online

DataTransformation

1. Relationship of 2 queries to 3rd one
   1. [NonSalesData].[RelatedOrder]->[ActiveSalesData].[RelatedOrder]
   2. [NonSalesData].[RelatedOrder]->[ExpiredSalesData].[RelatedOrder]
2. I also do a LOOKUPVALUE DAX function on the 2 SharePoint list to the ActiveSalesData or ExpiredSalesData
   1. The 
3. I have 2 UNION DAX function that stacks the 2 SharePoint list and 3 direct queries to make [ActiveBalanceTable] and [ExpiredBalanceTable] based on FILTER(SELECTCOLUMNS DAX functions

Power BI Desktop

1. This all works fine in PBI Desktop and refreshes in a couple of seconds
2. Can publish to PBI Service no problems with no errors

Power Service

1. Everything looks fine when I initially push from Desktop
2. If I schedule anything or manually refresh I always get this error and have to refresh in Desktop and push to Web version

"The query referenced table [ActiveBalanceTable] which depends on another column, relationship, or measure that is not in a valid state."

The only way this seems to work if I manually refresh in Desktop and keep overriding my dataset in Power BI Web version.

In digging into this a bit more I found the comparision of Power BI Desktop to Service.

Comparing Power BI Desktop and the Power BI service - Power BI | Microsoft Docs

The thing that's in Desktop but not in Service is Calculated Columns - which I think the [LOOKUPVALUE] function is.

Does that mean that if I have any of those DAX Function, it'll work in Desktop but it won't refresh in Service?  

Any other ideas?

[IncursioML1]

So I went through every single column of the dataset and changed everything to be consistent as much as I can.

I changed all the 'any' data types from SharePoint Online to either Text or Whole Numbers, those are ones I don't use anyways so not sure if it mattered.

Then validated the DAX formulas and did find one thing that may be causing the issue though I am not sure.

SharePoint has a 'Quantity' column in the form of a number, in which PowerBI brings back as a whole number.

SQL Server has a 'Quantity' column in the form of a number, in which PowerBI brings back as a likely as a decimal. (There are negatives in SQL Server query but no negatives in SharePoint).

When I created a table out of these two with a UNION formula -it is most definitely keeping the decimal as the datatype, which makes sense since negatives aren't in whole numbers.  I then went back to Power BI's imported SharePoint Online dataset and see if I could change the datatype from whole numbers to decimals, to which I can't.  

Just for giggles I created another SharePoint Online list and set a field 'Quantity' to be Number but formatted it to 2 decimals places.  Lo and behold when I imported this to PowerBI it treated it as a text, in which case it allowed me to change the format to a Decimal in PowerBI.  I'm actually going to go back and see what happens when I change that SharePoint List of 'Quantity' I had in the original and reimport and change the value to Decimal to match up to the SQL query as decimals.  I hope I'm not going down a rabbit hole, but it seems to me that this could be one of the reasons the Web version of Power BI could be having problems.

Quantity is literally the only field that should be a number but is joined up with the SQL data to do math in the ending UNION tables.  I also have Dates in there from both SharePoint Online and SQL Server to which the DAX calculations are treating it like a Date, so that checks out, same with anything Text datatype.

[collinq]

Hi @IncursioML1 ,

The first thing to check is to confirm that no columns being used in the visuals, or the links between tables, or in a DAX statement are in the "any" data format.  If that part is true, then confirm that you have number data format in the right format and that DAX isn't using one "differently" than the format - in other words a "percentage" that you are using more like a "number".  Let us know if that resolves it!

[IncursioML1]

Do I need to just focus on the visuals, links on tables, or DAX statements on 'any' data format or should I change all the 'any' data format to something?  I see here that you are right in which SharePoint imported a number field that was set up in SharePoint Onlin as an 'any' which it is.  Let me try that out as you are suggesting and follow the breadcrumb - it's rather annoying how various data sources treat things differently but that's what we're dealing with.

[collinq]

Hey @IncursioML1 ,

Did that work out for you?

[IncursioML1]

Sorry it did not - upon investigating a bit more it seems to be a gateway error on refresh based upon permissions.

We have a physical machine with a shared login set up, but we are in process to set this up with a Windows account to give the same permissions to things like SharePoint List, Excel Online etc.  After we get that set up we can add that shared login to the gateway permissions and hope it'll refresh.