topic Re: ALL results diverging in Excel compared to PowerBI in DAX Commands and Tips

ALL results diverging in Excel compared to PowerBI

Anonymous — Mon, 07 Sep 2020 00:27:13 GMT

Hi All,

The All function is giving me divergent results from PowerBI to Excel. I want to calculate the annual budget regardless of the month selected on the slicer. To create this I have built the measure;

Measure ignoring month =calculate([Budget],ALL(CalendarMonth[Fiscal Month Name]))

Here are the results from PowerBI;

And here are the results from Excel;

It's working as expected in Excel, but what could be going on to produce the divergent results????

Re: ALL results diverging in Excel compared to PowerBI

amitchandak — Mon, 07 Sep 2020 01:15:56 GMT

Anonymous , one of the two should work as measure

Measure ignoring month =calculate([Budget],ALL(CalendarMonth))
Measure ignoring month =calculate([Budget],removefilters(CalendarMonth[Fiscal Month Name]))

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

Re: ALL results diverging in Excel compared to PowerBI

Anonymous — Mon, 07 Sep 2020 01:26:01 GMT

Thanks amitchandak , I tried allexcept fiscal year - whihc seems to hahve fixed my problem I still cant explain why there were different results though.

Re: ALL results diverging in Excel compared to PowerBI

amitchandak — Mon, 07 Sep 2020 01:28:06 GMT

Anonymous , In that case, it should keep filter of year and remove everything else.

Re: ALL results diverging in Excel compared to PowerBI

Greg_Deckler — Mon, 07 Sep 2020 01:33:27 GMT

Anonymous - Really hard to troubleshoot without sample data. Are you certain that you do not have something like a page level filter in your Power BI report? Page level filters and report level filters prefilter the data before it gets to DAX so even an ALL won't bring the data back once it is filtered by page and report level filters.

Re: ALL results diverging in Excel compared to PowerBI

Anonymous — Mon, 07 Sep 2020 05:00:00 GMT

Understandable - re no data.

I think my observation is a quirk. MattAllington details what is happeing here;

https://exceleratorbi.com.au/the-all-function-in-dax/

Cheers

Mark

Re: ALL results diverging in Excel compared to PowerBI

Anonymous — Mon, 07 Sep 2020 11:00:28 GMT

You are probably sorting Fiscal Month Name by another column in the underlying model table... This is why ALL is not enough when applied to the column. This is a known issue and if you want to know more about it, you should head to www.sqlbi.com and look for an article about problems with sorting.