Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
Greg_Deckler
Super User
Super User

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

Or

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Komal_chouhan
Regular Visitor

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

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

Or

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

That worked. Thanks very much for your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.