Forum Discussion

clay_75's avatar
clay_75
Frequent Visitor
2 years ago
Solved

Dealing with granularity - best practice

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 gran...
  • mark_endicott's avatar
    mark_endicott
    2 years ago

    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's avatar
clay_75
Frequent Visitor
2 years ago

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's avatar
    mark_endicott
    Icon for Super User rankSuper User
    2 years ago

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