topic Re: Dealing with granularity - best practice in DAX Commands and Tips

Dealing with granularity - best practice

clay_75 — Tue, 21 May 2024 06:49:37 GMT

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

Re: Dealing with granularity - best practice

clay_75 — Tue, 21 May 2024 07:13:53 GMT

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

Re: Dealing with granularity - best practice

mark_endicott — Tue, 21 May 2024 08:53:00 GMT

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.

Re: Dealing with granularity - best practice

clay_75 — Thu, 23 May 2024 03:03:15 GMT

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.

Re: Dealing with granularity - best practice

clay_75 — Thu, 23 May 2024 03:05:58 GMT

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

Re: Dealing with granularity - best practice

mark_endicott — Thu, 23 May 2024 08:21:46 GMT

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

Re: Dealing with granularity - best practice

mark_endicott — Thu, 23 May 2024 08:24:37 GMT

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.