Group By with where clause in DAX

[Anonymous]

Hi Team.

I am new to powerbi. I need your help. I have a below table. In this table, I would like to get the time difference between

ARGO( component)  STARTED( status) and FTS( component)  ENDED ( status) for all distinct tracking IDs( group by).

Thanks

Vibhu

[grazitti_sapna]

Hey,

For solving this problem you can use create a new measure which will allow you to calculate the time difference between the two and get the desired output.

Time difference: =

IF (

    HASONEVALUE ( Data[tracking ID] ),

    CALCULATE (

        SUM ( Data[time] ),

        Data[component] = "FTS",

        Data[status] = "ENDED"

    )

        - CALCULATE (

            SUM ( Data[time] ),

            Data[component] = "ARGO",

            Data[status] = "STARTED"

        )

)

[Anonymous]

Thanks Jhiwan for your quick response.I need one more help how to get average of "Time diff measure" for all tracking id and show in card.?

[Jihwan_Kim]

Hi,

Thank you for your message.

Please check the below whether it shows what you expected.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

Time diff measure: =
IF (
    HASONEVALUE ( Data[Tracking ID] ),
    CALCULATE (
        SUM ( Data[Time] ),
        Data[Component] = "FTS",
        Data[Status] = "ENDED"
    )
        - CALCULATE (
            SUM ( Data[Time] ),
            Data[Component] = "ARGO",
            Data[Status] = "STARTED"
        )
)

[Anonymous]

Hi @Jihwan_Kim  thanks for your response I have one more ques. if time is coming in epoch timestamp then i am getting the wrong result. can you please help me with this.?