Need help counting rows on tables that are not directly related

[msimmonsmcse]

I have a data model that contains 3 tables: Tickets, Time Entry, and Members

• Tickets and Members have a 1:* relationship witht the Time Entry table. 
• Each row in Members has a unique MemberID
• Each row in Tickets has a unique ServiceTicketRecID.
• Each row in Time Entry is related by MemberID and ServiceTicketRecID respectively
• Each row in Tickets also has a ResourceList column that contains one or more MemberIDs in a string datatype. The column can contain one or more MemberIDs

I want to created a caluated column called Assigned Ticket Count for each row of the Member table. Here is the formula I tried first:

Assigned Ticket Count = CALCULATE(COUNT(Tickets[ServiceTicketRecId]), CONTAINS(Tickets,Tickets[ResourceList], 'Member'[MemberId])=TRUE())

However, I got an error "A function 'CONTAINS' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

So I tried:

Assigned Ticket Count = CALCULATE(COUNT(Tickets[ServiceTicketRecId]), FILTER(Tickets,CONTAINS(Tickets,Tickets[ResourceList], 'Member'[MemberId])=TRUE()))

Now I am getting the same numbe in all rows; its probably the total number of rows in the Tickets table. How do I get this to show the correct number of Tickets that contain the MemberID in the ResourceList field for each row in Members?

[v-alq-msft · Accepted solution]

Hi, @msimmonsmcse 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Tickets:

Members:

You may create a calculated column as below.

Assigned Ticket Count = 
var _result = 
COUNTROWS(
    FILTER(
        Tickets,
        CONTAINSSTRINGEXACT([ResourceList],EARLIER('Members'[MemberID]))
    )
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msimmonsmcse]

Worked great, thank you for supplying a solution instead of simply giving instructions on how to ask the question. Sometimes beginners dont even know what they are asking for. I appreciate it!

[Greg_Deckler]

@msimmonsmcse - Having trouble following this. Sample data would help but in theory, something like:

Column = 
  COUNTROWS(FILTER('Other Table',[column in other table]=[column in this table] && [another column in the other table] = [another column in this table]))

Otherwise,

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.