Detect when a distinct count exceeds a number.

[LeoQ]

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 ID	Department	Agent
1	Customer Service	John
1	Customer Service	Diana
2	Technical	John
3	Sales	Diana
3	Legal	Diana
4	Sales	Diana
5	Customer Service	John
5	Customer Service	Diana
5	Technical	John
5	Technical	Diana
5	Sales	John
5	Sales	Diana
6	Technical	Mary
7	Customer Service	John
7	Technical	John
7	Sales	John
7	Legal	John

Thanks in advance for your time!

[Jihwan_Kim · Accepted solution]

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], ", " ) )

Link to the sample pbix file 

[LeoQ]

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]

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 

[LeoQ]

It worked perfectly! Thanks a lot for your  time.

[aj1973]

Hi @LeoQ 

You want something that looks like this?

[LeoQ]

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!