Power Pivot and Power BI Desktop not returning the same result

[CastCS]

Hi,

I have a very odd problem that I don't understand. I have built a power pivot model in Excel and imported it into Power BI Desktop without any changes to the model. The problem is that the model in Power BI Desktop shows different figures compared to the ones the excelfiles calculates and that is because the measure counts some rows twice. The measure counts the number of IDs in the primary key field so there can't be any duplicates and when I go to Edit Queries and filter on one of the IDs that are counted twice only one row is returned. 

The measure is very straightforward, Registered sales= CALCULATE(COUNT([ID]);USERELATIONSHIP(dim_Calendar[Date];Installation[SalesDate])).

An easy solution is to use DISTINCTCOUNT instead, but that doesn't explain why the modell in Excel returns one figure and in Power BI Desktop another. Has anyone experienced anything similar and found a explanation / solution?

EDIT - Power BI Desktop shows the same figures as the Excel data model untill it is updated then the difference occurs.

[v-qiuyu-msft]

Hi @CastCS,

I'm not able to reproduce the issue like yours. Can you share your Excel workbook for us? Or could you share detail steps for us to reproduce the issue?

Best Regards,
Qiuyun Yu

[CastCS]

Hi,

Sorry, can't share the file with you, contains to much sensitive data.

The problem seems to occur when I merge queries, the numbers of rows in the left table are correct after the merge but for some reason there are duplicates of some rows which also means that others disappers.  This does not happen in the data model in the Excel file, and as I wrote, when filtering for one of the rows with duplicates in the data view, only one row is shown and it is also calculated as one row. 

The syntax of the merge below, does that help you to understand this?

    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"InstallationID"},AAASale,{"InstallationID"},"NewColumn",JoinKind.LeftOuter),

    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"SalesDate", "Company"}, {"AAASale.SalesDate", "AAASale.Company"}),

Best regards,
Caj