Forum Discussion
Measure to count where value appears in another table and using a filter for containsstring
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.
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")
)
)
2 Replies
- bhanu_gautam
Super User
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")
)
)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.
