Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have an on premise SSAS tabular model that has some MTD measures. When I build a report in Power BI Desktop, I am getting different results as compared to Excel pivot tables in some scenarios.
My model has an Invoice fact table, with a NetRevenue measure. One of the columns on the fact table is called Segment. The model also has a date dimension table.
I have created the following measures:
MTD Net Revenue:=CALCULATE(sum(Invoice[NetRevenue]), DATESMTD('Date'[FullDate]))
My fact table has the following:
Date | Segment | NetRevenue |
01-Sep-2016 | Segment A | 100 |
10-Sep-2016 | Segment B | 100 |
15-Sep-2016 | Segment C | 100 |
20-Sep-2016 | Segment A | 100 |
20-Sep-2016 | Segment B | 100 |
20-Sep-2016 | Segment C | 100 |
30-Sep-2016 | Segment C | 100 |
When I create a pivot table in excel, and put a filter of 30-Sep-2016, I get the following:
MTD Net Revenue | |
Segment A | 200 |
Segment B | 200 |
Segment C | 300 |
When I create a similar table in Power BI, I get the following:
MTD Net Revenue | |
Segment C | 300 |
The reason for this is that there is only data on the 30-Sep-2016 in the fact table for Segment C. The date filter is excluding the other 2 segments from the report.
Any help with this would be greatly appeciated. I'm happy to provide more details if you need it.
Thanks.
@cmn In this case power bi report is right then as you've applied date filter to 30-sep and there is only one segment against that date which is returned by power bi report.
Hi @ankitpatir. Thanks for your response, however the Power BI report is not right. The query is asking for the MTD values. Just because there was only one segment on 30th Sep doesn't change the fact that there was $200 worth of sales in the month for segments A and B. As I said, excel is doing the right thing.
As a further test, I created a very simple PBIX file with a very simple model consisting of the date table and an invoice table with the data shown in my original post. When I create a report against this model, PBI desktop is now doing the right thing. It is showing MTD values for all 3 segments. I have analysed the DAX being generated, and it is very different to that being generated against my on premise tabular model (SSAS 2014). I think the bug might be in the DAX that is generated against the old version of SSAS.
Here is the DAX generated using the PBIX model (using the latest DAX features)
DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('Date'[Date])), 'Date'[Date] = DATE(2016, 9, 30) ) EVALUATE TOPN( 502, SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL('Invoice'[Segment], "IsGrandTotalRowTotal"), __DS0FilterTable, "MTD_Net_Revenue", 'Invoice'[MTD Net Revenue] ), [IsGrandTotalRowTotal], 0, 'Invoice'[Segment], 1 ) ORDER BY [IsGrandTotalRowTotal] DESC, 'Invoice'[Segment]
and here is the DAX generated against my SSAS 2014 tabular model:
EVALUATE TOPN( 501, CALCULATETABLE( ADDCOLUMNS( KEEPFILTERS( FILTER( KEEPFILTERS(VALUES('Invoice'[Segment])), NOT(ISBLANK('Invoice'[MTD Net Revenue])) ) ), "MTD_Net_Revenue", 'Invoice'[MTD Net Revenue] ), KEEPFILTERS( FILTER(KEEPFILTERS(VALUES('Date'[Date])), 'Date'[Date] = DATE(2016, 9, 30)) ) ), 'Invoice'[Segment], 1 ) ORDER BY 'Invoice'[Segment]
Hi @cmn,
Has any other issues you faced on these steps?
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |