cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## creating a calculated column between two tables

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: creating a calculated column between two tables

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

Or

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

3 REPLIES 3
Super User

## Re: creating a calculated column between two tables

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

Or

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Frequent Visitor

## Re: creating a calculated column between two tables

That worked. Thanks very much for your help!

Frequent Visitor

## Re: creating a calculated column between two tables

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?

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 95 members 1,610 guests
Recent signins: