Power BI Null Handling from Azure Analysis Services - Issue

[csvdesk]

Starting 4/10/2017, reporting which has been in place for weeks has now started handling nulls incorrectly. Power BI is replacing nulls from the tabular cube with values (either zero, or with the values from another attribute all together). See screenshots. 

The Dax formula used to create the MonthTrend is below. I had to update the original formula to change nulls to zeros so reporting would be accurate. 

Original Formula
MonthTrend:=TOTALMTD(sum(Fact_Sales_Target[ActualUSDGross]),Dim_Date_Target[Date],Dim_Date_Target[Date]<datevalue(now()-6/24))/max(Fact_Sales_Target[MonthBusDaysElapsed])*max(Fact_Sales_Target[MonthBusDaysTotal])

Updated Formula to replace null with zero
MonthTrend:=if(TOTALMTD(sum(Fact_Sales_Target[ActualUSDGross]),Dim_Date_Target[Date],Dim_Date_Target[Date]<datevalue(now()-6/24))=0,0,TOTALMTD(sum(Fact_Sales_Target[ActualUSDGross]),Dim_Date_Target[Date],Dim_Date_Target[Date]<datevalue(now()-6/24))/max(Fact_Sales_Target[MonthBusDaysElapsed])*max(Fact_Sales_Target[MonthBusDaysTotal]))

[siddhartha]

It seems something has gone wrong with PowerBI April release. We are facing similar issues. It is happening with Integers and Decimal values in Tables , Matrix , Graphs and charts, however if put in slicers it shows actual values.

[v-haibl-msft]

@csvdesk

I cannot repro the same issue as you while connect to my AS cube. Power BI Desktop can display the nulls properly on my side. Do you have this issue both in PBI Desktop and Service? Could you please provide fiddler trace for your issue?

Best Regards,
Herbert

[csvdesk]

Yes, it is happening in both the online service and in desktop. I have captured a Fiddler trace on my Power BI session. How do you want the trace delivered, or do you only want a portion of the trace? If so, please let me know what portion you need from the trace. 

I have also confirmed that the issue is still present in my reporting by removing the workaround I put in place on Monday 4/10. 

Thanks in advance for the help provided. 

[siddhartha]

Hi Herbert,

Can you try a simple tabular cube ( on Azure Analysis Services)with the following query as one table and try to create a power bi table ( or anything else)?

SELECT CAST(Col1 AS VARCHAR(10)) AS Col1,
CAST(Col2 AS INT) AS Col2,
CAST(Col3 AS Decimal(10,2) ) AS Col3
FROM (
select 'A' col1, NULL Col2, 3.56 Col3
UNION ALL
select 'B' col1, 5 Col2, NULL Col3
UNION ALL
select NULL col1, 156 Col2, 52.67 Col3
) a

Please let me know if you are able to reproduce the issue.

Thanks

Sid

[v-haibl-msft]

@csvdesk @siddhartha

I can repro the same issue as you with live mode now. I’ve reported it internally to Power BI Team: CRI 35672286
I’ll post here once I get any update about it.

Best Regards,
Herbert

[v-haibl-msft]

@csvdesk @siddhartha

The fix is being deployed. The ETA is 4/17

Best Regards,
Herbert

[siddhartha]

Thanks Herbert. It seems to be working.

I will do few more rounds of testing and get back in case there is any more issue.

regards

Sid

[csvdesk]

Thanks to both of you for the help and prompt responses. The fix appears to have resolved the problem.