Power Pivot CALCULATE vs. RELATEDTABLE

[Redoute]

I'm a beginner with Power Pivot/DAX, and got thinking about that:

Given two tables: items and groups, related by columns [groupid]. I create calculated columns in the groups table. There is more than one way to do it:

groups[n items 1]=COUNTROWS(RELATEDTABLE(items))
groups[max fact 1]=MAXX(RELATEDTABLE(items), items[somefact])

groups[n items 2]=CALCULATE(COUNTROWS(items))
groups[max fact 2]=CALCULATE(MAX(items[somefact]))

A third approach is defining measures in the items table, then use the measures in calculated columns:

items[n items]:=COUNTROWS()
groups[n items 3]=[n items]
items[max fact]:=MAX(items[somefact])
groups[max fact 3]=[max fact]

Assuming my tables will be large at some time, are there pros and cons?

 

[Matt Allington]

Don’t do any of these. Use measures instead.
https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

 

[Redoute]

Thanks for your advice.

I think your rules #1 (results will be counted in a pivot table) and #3 (low cardinality) may apply. However I will probably enhance the underlying PostgreSQL query instead of using DAX calculated columns.

I made an Excel file for playing with one kind of report I want to create. Took me a lot of try and error to create measures that seem to work. If interested please download and review https://forplan-my.sharepoint.de/:x...lGtf8cQlT08GwBRfP8s8U6ezX56P-ZKwb6tQ?e=mVNVhG.