Dealing with granularity - best practice

[clay_75]

Hi all, 

New to DAX and Power BI - wanting to know how to handle metrics of different granularity.

I'm using  an SQL view that returns order picking info - it includes multiple levels of granularity i.e.

- A cluster id (orders that were picked toghether

- order number

- product / location etc.

I have metrics such as "time to pick" which is at the product / locatino level and "cluster duration" which is at the cluster id level. Cluster duration is not just the sum of "time to pick" - it includes other time not necessarily spent picking. 

I've just created a report at the Cluster id level and realised that it's summing the repeated "cluster duration" - what's the best way to handle this? Do i create a new table with SUMMARIZE or something.?

e.g. this is returning the result i want in DAX Studio

EVALUATE
	SUMMARIZECOLUMNS(
		ClusterAudit[Task Reference],
		"Cluster Duration (mins)", MIN(ClusterAudit[Cluster Duration Mins]),
		"Pick Time (mins)", MIN(ClusterAudit[Pick Time Mins])
		)
ORDER BY
ClusterAudit[Task Reference]

Thanks for any assistance.

Clay

[clay_75]

Thanks Mark - that appears to work.

Picking Mins = 
    SUMX(
        SUMMARIZE(
            ClusterAudit,
            ClusterAudit[Task Reference],
            "Picking Mins", MIN(ClusterAudit[Cluster Duration Mins])
        ),
        [Picking Mins]
    )

This also appears to work - performance analyzer showing me the same numbers. 

Is there a difference?

Thanks for your help. 

[mark_endicott]

@clay_75 - No, this will result in exactly the same query plan. I just like to separate things out with variables for readability. 

[mark_endicott · Accepted 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. 

[clay_75]

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

[mark_endicott]

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. 

[clay_75]

Note, my inclusion of 

"Pick Time (mins)", MIN(ClusterAudit[Pick Time Mins])

might be confusing....this is just another metric at the ClusterAudit[Task Reference] level - it's not the same as the sum of "time to pick".  Time to pick measures the time between each pick and "Pick Time Mins" is the total duration minus any time where the task was suspended (e.g. lunch) > cluster duration includes start time to end time regardless of suspensions