Forum Discussion
Check several rows with same ID
Hi folks,
I am quite new to PowerBI and currently wokring on the following topic:
I do have messages and message_threads, both are in one table. Messages can be either OUTBOUND (sent by company) or INBOUND (sent by customer). I now want to count the number of active threads, meaning the number of threads where at least one message was sent by the customer.
| message-id | thread-id | message-type |
| 1 | 1 | OUTBOUND |
| 2 | 1 | OUTBOUND |
| 3 | 2 | OUTBOUND |
| 4 | 2 | INBOUND |
| 5 | 2 | OUTBOUND |
| 6 | 3 | OUTBOUND |
| 7 | 3 | OUTBOUND |
| 8 | 3 | INBOUND |
In this Case thread 2 and 3 are active threads (at least one message with type INBOUND). Thread 1 is not active.
The outcome I would expect would for example look like the following:
| message-id | thread-id | message-type | active |
| 1 | 1 | OUTBOUND | false |
| 2 | 1 | OUTBOUND | false |
| 3 | 2 | OUTBOUND | true |
| 4 | 2 | INBOUND | true |
| 5 | 2 | OUTBOUND | true |
| 6 | 3 | OUTBOUND | true |
| 7 | 3 | OUTBOUND | true |
| 8 | 3 | INBOUND | true |
Thanks a lot in advance 🙂
5 Replies
Hi Anonymous
please try
= IF ( ISEMPTY ( FILTER ( CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[thread-id] ) ), TableName[message-type] = "INBOUND" ) ), FALSE, TRUE )// Let the table be T. [# Active Threads] = CALCULATE( DISTINCTCOUNT( T[thread-id] ), DISTINCT( T[thread-id] ), T[message-type] = "inbound", REMOVEFILTERS( T ) )
