cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwinkels Frequent Visitor
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

3 REPLIES 3
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

mwinkels Frequent Visitor
Frequent Visitor

Re: creating a calculated column between two tables

That worked. Thanks very much for your help!

Komal_chouhan Frequent Visitor
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?

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 413 members 4,452 guests
Please welcome our newest community members: