Minimum difference per group

[Anonymous]

Hello there,
I have a table with the events produced by a series of clients on my website. This table is connected through Direct query and has more 40 million values.

I have another table with the calls of the identified clients.

I would like to make a KPI that indicates how many of the clients called after they surfed the web, with a maximum of 1 hour.

For example, this would be an intermediate calculation table:

But the result should be: 50% 

I've tried a thousand ways, but I can't get it. 

Thank you so much.

[v-gizhi-msft]

Hi,

According to your description, i create a measure to calculate the calls:

Measure = IF(CALCULATE(DATEDIFF(MAX(Events[Date_Hour]),MIN(Calls[Date_Hour]),MINUTE))<=60,1,0)

And it shows:

Then create a measure to show the percentage:

Measure 2 = CALCULATE(DISTINCTCOUNT(Events[ID_Person]),FILTER(Events,[Measure]=1))/COUNTROWS(DISTINCT(SELECTCOLUMNS(Events,"Date",Events[Date],"ID_Person",Events[ID_Person])))

And the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Thank you v-gizhi-msft

I have been testing your solution and touched it a bit so that Mesure would only take into consideration the positive values not 0.

This way it only takes into account later calls.

Measure = 
IF (
    AND (
        CALCULATE (
            DATEDIFF ( MAX ( Events[Date_Hour] ); MIN ( Calls[Date_Hour] ); MINUTE )
        ) <= 60;
        CALCULATE (
            DATEDIFF ( MAX ( Events[Date_Hour] ); MIN ( Calls[Date_Hour] ); MINUTE )
        ) > 0
    );
    1;
    0
)

However, when using the minimum of all calls, (MIN ( Calls [Date_Hour]), in case the user has called on different days it doesn't take into account the nearest call. For example, in the example, the last cccc value actually has a call 5 minutes later.

I would need the positive difference closest to the event to be calculated.I would need the positive difference closest to the event to be calculated. Since it is different for the event to occur and call the client than the other way around.

I attached the file with the new formulas and the connection between id_person of the two tables.

https://we.tl/t-QEZNE2EEkk

Thank you very much.

[Anonymous]

Could someone help me?
Thank you very much.