Odd problem reading data via ODBC link

[Joham]

Hey all,

I'm relatively new to PowerBI so if I'm not clear, please bare with me.

I was tasked with creating a duplicate (or as near as possible) report in Power Bi from a Crystal report.

So far I've managed to:

• Import the data sets I needed via an ODBC link.
  • Delete the information from those data sets that wasn't needed with the help of the original creator of the crystal report.
  • Create the relationships between the data sets required.
• Create the Power BI report layout (basically an excel table with rows and columns) with the required information.
  • Create the formula's for specific columns.
• Rename the columns so it's user friendly.
• Apply the correct filters (per Crystal report).
• Create a Date table with various date and time options.
  • Create the relationship with this date table and link it with my other tables successfully.

The issue I'm having is when I compare the newly create Power BI report to the Crystal Report, I'm finding that there's two columns of data that are incorrect values. These columns read data directly from my original ODBC data set.

For example, say Column A (data set; LANS.A) and Column B (data set; LANS.B) are showing 10 and 10 respectively on Power BI report, where as on Crystal (looking at the same data set) say 20 and 10 respectively instead.

This then throws off the calculations done in other columns thus making the Power BI report incorrect.

I'm not intirely sure what's causing this issue and I'm not too sure where to look. Would it be a relationship issue? Could it be that the data I'm trying to read just isn't made to work with Power BI? Keeping in mind, I didn't design nor do I manage the database I'm connected too.

If there's any other information you require or if you want a deeper explaination, please let me know.

Thanks in advance for any help, it's appreciated.

[v-diye-msft]

Hi @Joham ,

Please help post some captures , or dummy pbix is preferred. 

[Joham]

Hey @v-diye-msft ,

Thanks for replying to my question.

Below you can see the first row of my report. The issue I was mentioning before is to do with the report reading Ordered Quantity and Despatch Quantity incorrectly. These are just fields in a table, however to get them you need to make sure the relationships are correct as it requires an Account Code which comes from a different table.

First Row of my report

Below is the same row but in Crystal Reports (the report I'm trying to migrate to Power BI). As you can see, the ordered Quantity and Despatch Quantity are different. In this case, I was lucky in that the Value and Outstanding columns still came out the same due to the arithmatic involved.

First Row of the Crystal Report I'm m

Here are the relationships currently on the Power BI Report. They match the relationships that Crystal has. You can ignore the LastRefresh_Local table as I'm using that for a card.

Power BI Relationships #1Power BI Relationships #2

Below is how Outstanding is calculated. A simple formula.

How Outstanding is calculated

I'll look into providing a dummy report shortly, but for now will that do? Is there any other pictures that could help you?

Thanks again.

[v-diye-msft]

Hi @Joham ,

The funtion is different between crystal report and power bi report, it's not applicative to copy the relationship/formula from crystal report, please share me your dummy pbix (Upload it to Google drive/Dropbox/Onedrive and share the link here) and let me know the expected result , I'd like to provdie the further valuable advice.

[Joham]

@v-diye-msft,

That's good to know. I've sent you a private message regarding this issue. Could you respond when you get a chance please?