METHODs TO CARRY OUT DATA AGGREGATION USING DIRECT QUERY?

[Nachospt]

Here it is the situation.

• I started using POWER BI short ago and have just implemented DIRECT QUERY.
• I know some DAX (not much) and dont know about "M", POWER QUERY formulae.
• I have read i can't create calculated columns (DAX), and just can use "add custom column" that comes down to POWER QUERY formulae.

I want know the simplest way to handle aggregated/Grouped data. If menu buttons can work it out (instead of coding) better. I need to calculate ranking scores, filter according to these and probably also filter according dynamics dates/indexes.

Here my questions:

• Is it mandatory to learn "M" POWER QUERY formulae to do this?
• Is it posible to create costum columns using other custom columns in the formulae (much like measures/new columns work without DIRECT QUERY)?
• Is it a good idea to create new related subtables (that summarize) using "Group by" option in edit query? is it better to add a custom column in the same table (with rows in the same category having the same value)?
• New tables seems simpler but i might need then M-M relationships and bidirectional cross filtering, right? Does this affect the performance of the final application?

I hope i could express my questions clear enough. Just ask if need further explanation.

Thanks for your attention

[v-qiuyu-msft · Accepted solution]

Hi @Nachospt,

• Is it mandatory to learn "M" POWER QUERY formulae to do this?

No. You can also open Query Editor, then use Group By button to group data based on prefer field.

Or you needn't to do anything about data model, you can only place those fields in the table visual and check aggregate function like this:

• Is it posible to create costum columns using other custom columns in the formulae (much like measures/new columns work without DIRECT QUERY)?

In DirectQuery, it's also possible to create a measure and calculated column using DAX. You can take a look at this article: Ability to create measures when using DirectQuery mode.

• Is it a good idea to create new related subtables (that summarize) using "Group by" option in edit query? is it better to add a custom column in the same table (with rows in the same category having the same value)?

Yes, we can use "Group by" in edit query to group data, then build relationship betwene this new table with fact table. In my opinion, "Group by" is better.

• New tables seems simpler but i might need then M-M relationships and bidirectional cross filtering, right? Does this affect the performance of the final application?

In DirectQuery, relationship filtering is limited to a single direction, rather than both directions. You can use SQL Sevrer Profiler to minitor backend query execution time when you filter or refresh data. Also please take a look at Limitations of DirectQuery and Important considerations when using DirectQuery sections in this article: Use DirectQuery in Power BI Desktop.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Nachospt]

Thanks