Importing a data model from Excel to PowerBi: Different results for measures

[eod122]

Hello everyone,

first post in this forum so I'm looking forward to some great discussions!

I succesfully imported a data model from Excel Powerpivot to PowerBi: Sales, customers, dates – a pretty simple setup. Everything’s fine after the import – all tables and relations are there, and most measures deliver the same results.

However, certain measures are providing different results in Powerpivot and PowerBi and I have no idea why…

Are there maybe

• any known bugs when importing data models from Excel
• is DAX syntax slightly different for some formulas
• or are there settings in PowerBi which can cause differences
• or maybe additional settings that need to be applied to tables or relationsships?

One example of a measure that’s causing me trouble can be found below: „Customers who also bought the vear before“.

Delivers correct results in Excel, but very odd results in PBI.

Thanks for your help!

PowerPivot Table – this are the expected results:

PowerBI – Totals and new customers are identical, „Customers who also bought the vear before“ is wrong and reactivated customers is also wrong (but this is a simple subtraction of Totals minus the other two measures).

This is the formula I’m using which is working fine in Excel Powerpivot but not in PBI:

Customers who also bought the vear before =
CALCULATE(

   DISTINCTCOUNT(Sales[CustomerID]);Sales[Product]<>"Product3";Customer[Group]="Private";

   FILTER(

       VALUES( Customer[CustomerID] );

       CALCULATE( COUNTROWS( Sales ) ) > 0

   && CALCULATE(

            COUNTROWS(Sales);

           FILTER( ALL( Date [Year] ); Date [Year] = MAX( Date [Year] ) - 1 )

       ) > 0

   )

)

[v-xuding-msft]

Hi @eod122 ,

In theory, it will be shown normally when we imporeted the model from Excel. And we use DAX to calculate both in Power BI and Excel. You can try the following formula firstly.

Customers who also bought the vear before =
var a = CALCULATE ( COUNTROWS ( Sales ) ) 
var b =  CALCULATE (
                COUNTROWS ( Sales );
                FILTER ( ALL ( Date[Year] ); Date[Year] = MAX ( Date[Year] ) - 1 )
            )
return
CALCULATE (
    DISTINCTCOUNT ( Sales[CustomerID] );
    Sales[Product] <> "Product3";
    Customer[Group] = "Private";
    FILTER (
        VALUES ( Customer[CustomerID] );
        a >0 && b >  0
    )
)

If you can post a dummy file which we can test with that will help easier for us to solve it.  If you can't, please post some sample data.

How to Get Your Question Answered Quickly

Best Regards,

Xue Ding

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

[eod122]

Thanks for your reply!

I wasn't exactly able to solve it with the formula you suggested, however after playing around a bit I found that 

"VALUES ( Sales[CustomerID])" instead of "VALUES ( Customer[CustomerID] )" did the job and delivered the expected results.

I'm fine with that but still struggle to understand why the formula is now giving the same results in PowerBi despite the difference to the original PowerPivot formula, and why the PowerpIvot formula creates unexpected results when being imported into PowerBi...