Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
  • 1.  Measuring deflection rates

    Posted 5 days ago
    Edited by Michael Hutchens 5 days ago

    Hi folks,

    I'm hoping that someone can assist. I work for a customer support organization that has a self-service customer knowledge portal, and I'm wanting to put together a report that shows deflection rates. We have defined 'deflection' as:

    When a user has viewed an article, AND rated it within 12 hours of the view datestamp, AND when they have not logged an incident within 24 hours of the view datestamp

    I have three tables, one showing articles viewed, who viewed them, and the datestamp of the viewing. The second table shows articles that have been rated, who rated them, and the datestamp of the rating. The final table shows incidents created, who they were created for, and the datestamp of their creation.

    Table name: Viewed

    article viewed date article viewer article viewed id
    10/10/20 4:00 PM Jimmy Jimson 123456
    10/10/20 3:00 PM Sarah Sarahson 123456
    10/10/20 1:00 PM john johnson 123455
    11/10/20 10:00 AM peter peterson 123458
    10/10/20 4:00 PM marky markson 123789
    10/10/20 4:00 PM marky markson 123789
    10/10/20 4:00 PM marky markson 123790



    Table name: Ratings

    article rated date article rater article rated ID
    10/10/2020 4:10pm jimmy jimson 123456
    10/10/2020 3:01pm Sarah Sarahson 123456
    11/10/20 10:11 AM peter peterson 123458
    10/10/2020 4:10pm marky markson 123790
    10/10/2020 4:10pm marky markson 123789
    11/10/2020 4:10pm marky markson 123789


    Table name: Incidents

    incident created date incident caller name incident id
    10/10/20 5:00 PM jimmy jimson INC789000
    10/10/20 3:24 PM Sarah Sarahson INC789001
    10/10/20 2:45 PM john johnson INC789002
     
    11/10/20 5:00 PM marky markson INC789004
    12/10/20 5:00 PM marky markson INC789005
    10/10/20 3:43 PM marky markson INC789006


    I've experimented with a couple different table relationships, based on the user's names and also the datestamps, but haven't been able to get what I wanted. Ultimately I'm looking for a calculated table that shows columns for:

    • article viewer
    • article viewed id
    • article viewed date
    • article rated date

    Counting the lines of this calculated table should show me total deflections. Based on the sample data above, I'd expect the following to be counted as deflections:

    article viewed date article viewer article viewer id article rated date
    11/10/20 10:00 AM peter peterson 123458 11/10/20 10:11 AM
    10/10/20 4:00 PM marky markson 123789 10/10/2020 4:10pm


    Any assistance would really be appreciated - thanks so much! :)

    ------------------------------
    Michael Hutchens
    ------------------------------
    Academy - Online Interactive Learning from Experts