DAX vs M languace in PBI service: are there anuìy guidelines?

[gpiero]

I noticed, pubblishing several PBI desktop report on PBI service, that many DAX solutions that run perfectly on the desktop are incompatible ( or at least what I understood up to now) in the service.

For instance, I had a report where I created a table from another datasource using SUMMARIZE. It works fine as a desktop report but the refresh failed in the service.

Then I created the same table in a new report using Table.Group() and the refresh failing was solved.

This event and some others very similar convinced to prefer M query in the service instead of a lot of DAX.

But I am not sure that it is the best conclusion.

So my question is: where can I find some helpful guidelines or Current Best Approach to design in a proper way a report that should be published in PBI service, in order to decrease  the troobleshooting?

Thanks in advace

[v-frfei-msft]

Hi @gpiero ,

M and DAX are two completely different languages.

M is used in Power Query (a.k.a. Get & Transform in Excel 2016) and the query tool for Power BI Desktop. Its functions and syntax are very different from Excel worksheet functions. M is a mashup query language used to query a multitude of data sources. It contains commands to transform data and can return the results of the query and transformations to either an Excel table or the Excel or Power BI data model.

More information about M can be found here and using your favourite search engine.

DAX stands for Data Analysis eXpressions. DAX is the formula language used in Power Pivot and Power BI Desktop. DAX uses functions to work on data that is stored in tables. Some DAX functions are identical to Excel worksheet functions, but DAX has many more functions to summarize, slice and dice complex data scenarios.

There are many tutorials and learning resources for DAX if you know how to use a search engine. Or start here.

In essence: First you use Power Query (M) to query data sources, clean and load data. Then you use DAX to analyze the data in Power Pivot. Finally, you build pivot tables (Excel) or data visualisations with Power BI.

[gpiero]

@v-frfei-msft 

Hi,

many thanks for your detailed information. 

Furthermore I would like to thank you for two links regarding DAX and M included in you answer: it confirms I am using the right source of information.

Anyway, I believe the end user should have more specific tools to understand what is hidden behind an anonymous message:

"Refresh failed".

If a report built in PBI desktop can use SUMMARIZE, but the same command could not be solved in the service, so my question is: why the user should guess by himself how to solve the problem?

Thanks

[v-frfei-msft · Accepted solution]

Hi @gpiero ,

For the issue, that should be the your table has been updated. table name / column name was changed or columns were added / deleted. So to work on it, just delete the dataset and republish the report. BTW, please use the latest version of gateway and Power bi desktop to work on it.

[gpiero]

@v-frfei-msft 

Hi,

sorry for my late reply. Really I did not see your post dated last Jun 3rd.

Anyway before sending my initial post I modified the report. I replaced the DAX SUMMARIZE using Power Query with Table.Group().

At first test, the refresh works perfectly. I am quite sure between last use of SUMMARIZE and the first test with Table.Group nor change in table name was made neither in the column names.

In any case, just to complete this test I will try to duplicate the report putting SUMMARIZE again. I will pubblish the report in the service with a different name and in a TEST workspace.

Now I become really curious.

I will let you know.

[v-frfei-msft]

Hi @gpiero ,

Does that make sense? If so kindly mark my answer as a solution to close the case. Thanks in advance.