topic Re: Measure works in Power BI Desktop but not after publishing in DAX Commands and Tips

Measure works in Power BI Desktop but not after publishing

soundguy2 — Thu, 04 Jun 2020 22:09:50 GMT

I created a DAX measure to show me the total of the most recent day's sales, regardless of filter context. The measure seems to function correctly in Power BI Desktop, but any visuals using it produce an error message when published to Power BI Service.

The measure looks like this. Is there any reason it would work in Desktop but not on Power BI Service?

Latest Sales =
CALCULATE([Total Sales],
FILTER(ALL('Calendar'),
'Calendar'[Date] = MAX('Sales Data'[DateCreated])
)
)

Re: Measure works in Power BI Desktop but not after publishing

lbendlin — Thu, 04 Jun 2020 23:12:43 GMT

What does the error message say?

Re: Measure works in Power BI Desktop but not after publishing

soundguy2 — Thu, 04 Jun 2020 23:57:04 GMT

All it really says is that 'something's wrong' and points me to the affected measure. This only happens after it's been published.

Re: Measure works in Power BI Desktop but not after publishing

lbendlin — Fri, 05 Jun 2020 00:57:42 GMT

Can you confirm that your Calendar table on the service dataset is contiguous and covering? You can use DAX Studio with the service dataset as an XMLA endpoint to check that.

Re: Measure works in Power BI Desktop but not after publishing

soundguy2 — Fri, 05 Jun 2020 01:25:48 GMT

Hi Ibendlin,

I think you're on to something with the date table. The date table is generated from a script, so it's contiguous and covers the range of the data. However, it's stored as Datetime format and maybe this is conflicting with the server time when published to Power BI Service. I'll try changing to Date format and I'll post tomorrow whether that fixes the issue. Thanks for the direction.

Brian

Re: Measure works in Power BI Desktop but not after publishing

soundguy2 — Fri, 05 Jun 2020 16:48:05 GMT

It's working! You're correct that the issue was with the Date (calendar) table. Thanks for the help.

Regards,

Brian

Re: Measure works in Power BI Desktop but not after publishing

Anonymous — Wed, 10 Jul 2024 18:38:10 GMT

This issue was infuriating for me. I finally narrowed the issue down to a data type that I changed in my model not transferring over to the web service. So, instead of changing the data type in the model, I changed it within power query and that seemed to resolve the issue when publishing. Hope this helps