Check several rows with same ID

[lulubrinki]

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 🙂

[lulubrinki1]

Thank a lot @daXtreme @tamerj1 ,

I have to correct my initial table I posted. 

factMessages

Message-id	Thread-ID	MessageAttrKey
1	1	1
2		2
3	2	3
4	2	4

dimMessageAttributes

MessageAttrKey	Type	...
1	OUTBOUND
2	OUTBOUND
3	INBOUND
4	INBOUND

[daXtreme]

// Let the table be T.

[# Active Threads] =
CALCULATE(
    DISTINCTCOUNT( T[thread-id] ),
    DISTINCT( T[thread-id] ),
    T[message-type] = "inbound",
    REMOVEFILTERS( T )
)

[tamerj1]

Hi @lulubrinki 

please try

=
IF (
    ISEMPTY (
        FILTER (
            CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[thread-id] ) ),
            TableName[message-type] = "INBOUND"
        )
    ),
    FALSE,
    TRUE
)

[daXtreme · Accepted solution]

@tamerj1 

There's no need to use IF here since ISEMPTY is already a logical function. This is the same:

NOT ISEMPTY(
    FILTER(
        CALCULATETABLE(
            T,
            ALLEXCEPT(
                T,
                T[thread-id]
            )
        ),
        T[message-type] = "INBOUND"
    )
)

[tamerj1]

@daXtreme 

You are 100% correct. Thank you