creating a calculated column between two tables

[mwinkels]

I'm trying to create a calculated column that counts the number of actions taken by a person. In my case I have two tables. Table 1 has a list of unique email addresses. Table 2 has a list of actions taken by these unique email addresses (one row per action). I want to create a calculated column in Table 1 to count how many times the email address appears in Table 2.

The tables essentially look like this:

Table 1

email 1

email 2

email 3

Table 2

email 1

email 1

email 2

email 1

email 1

email 2

email 3

email 2

email 1

Ultimately I want Table 1 to have a new column that looks like this:

Column 1      column 2 (new calculated column)

email 1          5

email 2          3

email 3          1

I've tried a bunch of DAX formulas with no success (mostly trying to combine calculate() with various count functions). None of my usual standbys work. If this was excel, the equivalent formula would be countif().

The end goal is actually not to display the number. I need to use this calculated column apply additional logic. For example, we need to know when a person does at least 3 actions, so my next step will be to add a second calculated column with yes/no to indicate if the person has at least 3 actions.

Any suggestions you all have would be much appreciated.

[Komal_chouhan]

Hi, I am trying to create a calculated column by using two columns from two different tables in PowerBI desktop. But not able to select second column from other table. Can anybody help me into this?

[Greg_Deckler · Accepted solution]

MyCount = CALCULATE(COUNTROWS(RelatedTable),RELATEDTABLE(RelatedTable))

Or

MyCount1 = COUNTX(RELATEDTABLE(RelatedTable),RelatedTable[SomeColumn])

[mwinkels]

That worked. Thanks very much for your help!