Forum Discussion

LeoQ's avatar
LeoQ
Icon for Helper I rankHelper I
5 years ago
Solved

Detect when a distinct count exceeds a number.

Hi everybody, I spent lot of hours looking for a solution but I found nothing.

I have this table, that's made by a form completition. Each request might be made by different departments and might be solved by more than 1 agent. 

 

Whay I need is to detect when a request was made by 3 or more deparments and how many requests of this kind were solved by each agent. 

For example, John has 2 request of this kind (N° 5 & 7). 

I also have a date column, so it would be nice to filter by month as well.

 

Request IDDepartmentAgent
1Customer ServiceJohn
1Customer ServiceDiana
2TechnicalJohn
3SalesDiana
3LegalDiana
4SalesDiana
5Customer ServiceJohn
5Customer ServiceDiana
5TechnicalJohn
5TechnicalDiana
5SalesJohn
5SalesDiana
6TechnicalMary
7Customer ServiceJohn
7TechnicalJohn
7SalesJohn
7LegalJohn

 

Thanks in advance for your time!

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    Requests Count made by three dept or more : =
    VAR _newtable =
    ADDCOLUMNS (
    SUMMARIZE ( Data, Requests[Request ID] ),
    "@deptcount", CALCULATE ( COUNTROWS ( DISTINCT ( Data[Department] ) ) )
    )
    VAR _filtertable =
    FILTER ( _newtable, [@deptcount] >= 3 )
    VAR _summarizefilteredtable =
    SUMMARIZE ( _filtertable, Requests[Request ID] )
    RETURN
    IF ( HASONEVALUE ( Agents[Agent] ), COUNTROWS ( _summarizefilteredtable ) )
     
    Requests List made by three dept or more : =
    VAR _newtable =
    ADDCOLUMNS (
    SUMMARIZE ( Data, Requests[Request ID] ),
    "@deptcount", CALCULATE ( COUNTROWS ( DISTINCT ( Data[Department] ) ) )
    )
    VAR _filtertable =
    FILTER ( _newtable, [@deptcount] >= 3 )
    VAR _summarizefilteredtable =
    SUMMARIZE ( _filtertable, Requests[Request ID] )
    RETURN
    IF ( HASONEVALUE ( Agents[Agent] ), CONCATENATEX( _summarizefilteredtable, Requests[Request ID], ", " ) )
     
     
     
     
     

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

     

    Requests Count made by three dept or more : =
    VAR _newtable =
    ADDCOLUMNS (
    SUMMARIZE ( Data, Requests[Request ID] ),
    "@deptcount", CALCULATE ( COUNTROWS ( DISTINCT ( Data[Department] ) ) )
    )
    VAR _filtertable =
    FILTER ( _newtable, [@deptcount] >= 3 )
    VAR _summarizefilteredtable =
    SUMMARIZE ( _filtertable, Requests[Request ID] )
    RETURN
    IF ( HASONEVALUE ( Agents[Agent] ), COUNTROWS ( _summarizefilteredtable ) )
     
    Requests List made by three dept or more : =
    VAR _newtable =
    ADDCOLUMNS (
    SUMMARIZE ( Data, Requests[Request ID] ),
    "@deptcount", CALCULATE ( COUNTROWS ( DISTINCT ( Data[Department] ) ) )
    )
    VAR _filtertable =
    FILTER ( _newtable, [@deptcount] >= 3 )
    VAR _summarizefilteredtable =
    SUMMARIZE ( _filtertable, Requests[Request ID] )
    RETURN
    IF ( HASONEVALUE ( Agents[Agent] ), CONCATENATEX( _summarizefilteredtable, Requests[Request ID], ", " ) )
     
     
     
     
     
    • LeoQ's avatar
      LeoQ
      Icon for Helper I rankHelper I
      5 years ago

      Wow! You are awesome!

      Thanks a lot for taking the time to create de pbix file. It wordked perfectly.

      What function do you recommend if, instead of showing all the IDs concatenated, I show them in a multiple row card or another table.

      Thanks again!

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        Hi,

        Thank you for your feedback.

        I am not sure how you want to see your final outcome, but please check the below picture and the link to the sample pbix file.

         

         

         

        Link to the sample pbix file 

         

         

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi LeoQ 

    You want something that looks like this?

     

    • LeoQ's avatar
      LeoQ
      Icon for Helper I rankHelper I
      5 years ago

      Thanks for your time! I need to know how many requests with 3 or more departments solved each agent. Fortunately I got the solution. 

      Regards!