Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Minimum difference per group

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.

 

Need Help

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        Could someone help me?
        Thank you very much.