cancel
Showing results for
Did you mean:
Highlighted
mwinkels 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

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

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

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

Proud to be a Datanaut!

mwinkels Frequent Visitor

Re: creating a calculated column between two tables

That worked. Thanks very much for your help!

Komal_chouhan 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 Top Kudoed Authors
Users Online
Currently online: 95 members 1,610 guests
Recent signins:
• HarCopy • triyusniadi-672 • aqavi10 • s01330 • MMajeed • wasitari • Nagpraveen • triyusniadi-672 • ZhenX • Phil_Hodgetts • scottjd • harris821731 • RenySantoso • shahaka 