Calculating values based on groups (DAX Problem)

[Anonymous]

Hi,

I have posted this in the forum before but unfortunately I havent recieved a working solution, so thought I would try again.

I want to calculate the date difference between the two times (veu_time_mobile vs veu_time_at_scene) for the group of veu_fk_inc. (Included a screenshot of a sample of the dataset below)

My dax that im currently using is below:

Departure - In Attendance =
CALCULATE (
    DATEDIFF (
        MIN ( veu_vehicle_usage[veu_time_mobile] ),
        MIN ( veu_vehicle_usage[veu_time_at_scene] ),
        SECOND
    ),
    FILTER (
        veu_vehicle_usage,
        veu_vehicle_usage[veu_fk_inc] = EARLIER ( veu_vehicle_usage[veu_fk_inc] )
    )
)

However I need to modify this dax so that it picks up and compares the date difference between the two highlighted cells. I need it to ignore calculating it against records where the time at scene is less than the time mobile but to still calculate it for the group of veu_fk_inc.

Is anyone able to help me please?

Thanks

[Anonymous · Accepted solution]

For anyone wondering I used the below dax to solve my issue:

Dep-InAtt =
VAR min_mobile =
CALCULATE (
MIN ( 'veu_vehicle_usage'[veu_time_mobile] ),
FILTER (
ALL ( 'veu_vehicle_usage' ),
'veu_vehicle_usage'[veu_fk_inc] = EARLIER ( veu_vehicle_usage[veu_fk_inc] )
)
)
VAR min_at_scene =

CALCULATE (
MIN ( 'veu_vehicle_usage'[veu_time_at_scene] ),
FILTER (veu_vehicle_usage,
'veu_vehicle_usage'[veu_fk_inc] = EARLIER ( veu_vehicle_usage[veu_fk_inc] )
&&veu_vehicle_usage[veu_time_at_scene]
>= min_mobile


)
) // Brings back a blank if the time at scene is less than the time mobile
RETURN
DATEDIFF ( min_mobile, min_at_scene, SECOND )

[v-yingjl]

Hi @Anonymous ,

Try to modify your calculated column like this:

Departure - In Attendance =
VAR max_at_scene =
    CALCULATE (
        MAX ( 'veu_vehicle_usage'[veu_time_at_scene] ),
        FILTER (
            ALL ( 'veu_vehicle_usage' ),
            'veu_vehicle_usage'[veu_fk_inc] = EARLIER ( veu_vehicle_usage[veu_fk_inc] )
                && 'veu_vehicle_usage'[veu_vehicle_type]
                    = CALCULATE (
                        MIN ( 'veu_vehicle_usage'[veu_vehicle_type] ),
                        ALLEXCEPT ( veu_vehicle_usage, veu_vehicle_usage[veu_fk_inc] )
                    )
        )
    )
VAR min_moblie =
    CALCULATE (
        MIN ( 'veu_vehicle_usage'[veu_time_mobile] ),
        FILTER (
            ALL ( 'veu_vehicle_usage' ),
            'veu_vehicle_usage'[veu_fk_inc] = EARLIER ( veu_vehicle_usage[veu_fk_inc] )
                && 'veu_vehicle_usage'[veu_vehicle_type]
                    = CALCULATE (
                        MAX ( 'veu_vehicle_usage'[veu_vehicle_type] ),
                        ALLEXCEPT ( veu_vehicle_usage, veu_vehicle_usage[veu_fk_inc] )
                    )
        )
    )
RETURN
    DATEDIFF ( min_moblie, max_at_scene, SECOND )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl  Thanks alot for this,

it almost worked, i ended up tweaking it slightlly to get the result i needed. I will post the dax i used and mark it as the solution.

Thank you

[ryan_mayu]

@Anonymous 

please try this measure

Measure 2 = 
VAR minmobile=min(veu_vehicle_usage[veu_time_mobile])
VAR _scene=maxx(FILTER(veu_vehicle_usage,veu_vehicle_usage[veu_time_mobile]=minmobile),veu_vehicle_usage[veu_time_at_scene])
VAR _scene2=MAXX(FILTER(veu_vehicle_usage,veu_vehicle_usage[veu_time_at_scene]<_scene),veu_vehicle_usage[veu_time_at_scene])
return DATEDIFF(minmobile,_scene2,SECOND)

[Greg_Deckler]

@Anonymous If you could post sample data as text this would be easier to vet out. But I believe you want something like the following:

Measure = 
  VAR __VeuFxInc = MAX('Table'[veu_fx_inc])
  VAR __Mobile = MINX(FILTER('Table',[veu_fx_inc]=__VeuFxInc),[veu_time_mobile])
  VAR __Scene = MINX(FILTER('Table',[veu_fx_inc]=__VeuFxInc && [veu_time_at_scene]>__Mobile),[veu_time_at_scene)
RETURN
  __Scene - __Mobile