count if text is in a related table

[Jasel]

Hello all,

Hopefully this is an easy question with an uncomplicated answer.

I have a fact table with a related table in a one to many relationship.

Table1                      Table2

ID                             ID

                                 Text

If I create a measure on Table2 using  - IsXXXX = CALCULATE(COUNTROWS('Table1'), 'Table2'[Text] = "XXXX")  I get the same amount for any type of search criteria.  XXXX would = 2468, YYYY would = 2468, and ZZZZ would = 2468.

If I tryig and create a calculated column on Table1 using an if statement it doesn't see the relationship to table2 so I can't use:

if(Table2[Text] = "XXXX", 1,0).  I would create a column for each type of Text.

I'm assuming I'm thinking of this incorrectly.  If someone could point me towards the correct path I'd appreciate it.  If the solution uses some crazy code I'd appreciate an explination, not just a code dump as I like to learn and understand what is going on here.

Thanks!

[EvertonPessoa · Accepted solution]

Hi Jasel,

If the data is related because you do not account everything in table2?

IsXXXX = CALCULATE(COUNTROWS('Table2'), 'Table2'[Text] = "XXXX")

In the case of the column calculated to see the relation you can use the 'related' function:

if(related(Table2[Text]) = "XXXX", 1,0)