Forum Discussion
Dealing with granularity - best practice
clay_75 - If you would just like to find the Cluster Duration SUMMARIZE will work, you just need to create it as a virtual table, inside a variable (VAR) in a measure, then iterate through that varible to return it for each Task Reference.
I've amended your DAX slightly below, but you'll see from the screenshot this is tested and works:
Cluster_Duration = VAR _table = SUMMARIZE ( VALUES ( 'Table (7)'[Task Reference] ), "Duration Mins", MIN ( 'Table (7)'[Cluster Duration] ) ) RETURN SUMX ( _table, [Duration Mins] )If this works for you, please accept it as the solution.
clay_75 - If you would just like to find the Cluster Duration SUMMARIZE will work, you just need to create it as a virtual table, inside a variable (VAR) in a measure, then iterate through that varible to return it for each Task Reference.
I've amended your DAX slightly below, but you'll see from the screenshot this is tested and works:
Cluster_Duration =
VAR _table =
SUMMARIZE (
VALUES ( 'Table (7)'[Task Reference] ),
"Duration Mins", MIN ( 'Table (7)'[Cluster Duration] )
)
RETURN
SUMX ( _table, [Duration Mins] )
If this works for you, please accept it as the solution.
I've also added this to my source query
DENSE_RANK() OVER (ORDER BY CI.[Task Reference]) AS TaskId,
So that i can summarize by an integer rather than a string - i have about 6 task level measures to aggregate. I'm only guessing an integer will be better....any comment on this much appreciated.
Clay
Intergers make better surrogate keys than strings, yes. They are more optimal for data storage due to the types of compression the Vertipaq engine uses.