Distinct number of rows with certain description

[newbie_but_conf]

Hi,

I'm sure this has been asked before, but couldnt find anything that worked for me. 

I have something that looks like this table:

ID	Description	Number
1	Message	1
1	Search	1
1	Else	2
1	Complain	2
1	Message	3
1	Search	4
2	Message	1
2	Search	2
2	Else	2
2	Complain	2
3	Message	2
3	Search	2
3	Else	3
3	Complain	4
3	Message	4

I would like to get the total number of rows when Description is "Message" or "Search", but only count 1 row, when both happen and ID and number are equal. 
I've tried something like this:

Distinct rows = 
CALCULATE(
    DISTINCTCOUNT('Table1'[Number]),
    'Table1'[Description] in {"Message", "Search"},
    GROUPBY('Table1', 'Table1'[ID])
)

When I filter by ID, it seems to work as expected, however, when I try to get the total number of rows it just adds the distinct number. I've also tried to get just get the count, but that returns more rows than what I'm looking for.. 

As an example I expect a total count of 7 for the table I've added. That means counting just 1 for ID 1 with number 1 and counting 1 for ID 3 and Number 2.

Hope it makes sense.

Thanks!

[tackytechtom · Accepted solution]

Hi @newbie_but_conf,

How about this? 🙂

Distinct rows = 
VAR _helpTable = 
SUMMARIZE (
    Table3,
    Table3[ID],
    Table3[Number],
    "CountMesSearch", CALCULATE ( COUNTROWS ( Table3 ), Table3[Description] in {"Message", "Search"} )
)
RETURN
COUNTROWS ( FILTER ( _helpTable, [CountMesSearch] <> BLANK() ) )

Let me know if this solves your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[newbie_but_conf]

Just what I was looking for! Thank you!