Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Power BI Report Builder Column Expressions Error?

I have 2 columns in my report which use expressions which, when they're NOT hidden, they cause an object error. However, when their columns are respectfully hidden, I'm able to navigate through the report without any issues. Any assistance with these expressions as to what's wrong with them would be appreciated. This report used to be supported on SQL Server 2008 R2 Report Builder 3.0 and we've transitioned to a new Sharepoint which is backed by Power BI.  I've tried pushing it up to my Power BI Service (premium per user account), it acts like it's loading the rows of data then errors (see below). I've ensured my Gateway within my Power BI Service is accurate and properly connected.

 

Power BI Report Builder version: 15.7.1704.1

 

1st Column Expression:

 

 

 

=IIF(Lookup(Fields!SSN.Value,Fields!NSSN.Value,Fields!itemnum.Value,"dsNautilusPhotoID") Is Nothing,
IIF(Lookup(Fields!SSN.Value,Fields!NSSN.Value,Fields!itemnum.Value,"dsNautilusPhotoIDOLD") Is Nothing,"No","Old"),"Yes")

 

 

 

 

2nd Column Expression:

 

 

 

=IIF(Lookup(Fields!DAcctNbr.Value,Fields!NAcctNbr.Value,Fields!itemnum.Value,"dsNautilusChecklist") Is Nothing,"No","Yes")

 

 

 

 

Power BI Report Builder Error:

 

Power BI Service Error:

 

Edit:

Not sure if it's worth noting, but one DS is an Oracle via an ODBC connection and the other is an SSMS connection. Both expressions use a combination of both data sources. I've made sure the 32 and 64-bit ODBC drivers were added onto my reporting server and locally as well.

Need Help

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

     

    • Object reference not set to an instance of an object

     

    This is usually happens due to presence of empty cells don’t have null value. To figure out which sheet contains these cells, refresh the file. During the refresh, the error will appear in the culprit sheet saying for example “Customers sheet contains empty cells in the emails column”.

     

    You have to fill these cells either with null value or the missing data.

    For a similar question you can refer to this post.

    https://community.powerbi.com/t5/Issues/object-reference-not-set-to-an-instance-of-object/idi-p/1018975 

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Unfortunately, this has not resolved my issue nor does it tell me what cells are either with null value or missing data. Even when I hit "Refresh" after the report is generated and after the error (or even before), nothing is disclosed as far as "....culprit sheet saying for example “Customers sheet contains empty cells in the emails column”". Any columns which had a null value, I replaced with a string value of "Unknown" or I've also tried to just remove any rows with any null value in a particular column....still same error.