Measure to count where value appears in another table and using a filter for containsstring

[Dali7]

Hi there

I have produced the tables below based on dummy scenario that matches my actual data, I have two tables that are linked in many to many relationship under Technical Service:

Table 1 - all incidents

Technical Service	Title	Created by
Windows	Freeze session	Joe Bloggs
Adobe	Freeze unable to save	A Smith
Office	unable to save	P Jones

Table 2 - Type 1 incidents

Technical Service

Windows

Oracle

Office

The measure needs to count all the incidents under Table 1 by the created by column, in each row where the Technical Service in Table 1 is also contained within Table 2 Technical Service column. There then needs to be a filter for CONTAINSSTRING in Table 1 Title column for the word "Freeze".

This will then be used in a table visualisation looking something like this:

Created by	Count of Freeze
Joe Bloggs	1 (as Technical service appears in both tables and title contains the word Freeze
A Smith	0 (as Adobe is not listed in Technical Service Table 2)
P Jones	0 (as title does not contain the word Freeze)

I've been searching through the forum using LOOKUPVALUE and RELATED and can identify similar measures but they don't quite match what I am looking for and can't amend them correctly to fit my example.

Many thanks for any help you can provide.

[bhanu_gautam · Accepted solution]

@Dali7 , Ensure that there is a relationship between Table 1 and Table 2 based on the Technical Service column.

Then use DAX to create a measure that counts the incidents where the Technical Service in Table 1 is also in Table 2 and the Title contains the word "Freeze".

DAX
Count of Freeze =
CALCULATE(
COUNTROWS('Table 1'),
FILTER(
'Table 1',
'Table 1'[Technical Service] IN VALUES('Table 2'[Technical Service]) &&
CONTAINSSTRING('Table 1'[Title], "Freeze")
)
)

[Dali7]

@bhanu_gautam OMG thank you so much for such a quick response and it has worked perfectly, it was the IN VALUES that I wasn't familar with and that I was struggling with, thanks again.