Thread: Power Pivot CALCULATE vs. RELATEDTABLE Thanks: 0 Likes: 0

1. Power Pivot CALCULATE vs. RELATEDTABLE

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:
Code:
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:
Code:
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?  Reply With Quote

2. Re: Power Pivot CALCULATE vs. RELATEDTABLE

Don’t do any of these. Use measures instead.
https://exceleratorbi.com.au/calcula...-measures-dax/  Reply With Quote

3. Re: Power Pivot CALCULATE vs. RELATEDTABLE

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:...wb6tQ?e=mVNVhG.  Reply With Quote

User Tag List

Tags for this Thread

calculate, calculated columns, dax, power pivot, relatedtable  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•