Counting cells in related table with powerpivot

Question

In powerpivot i have two tables, and in both of them i have a column called ID, and ID is not unique on both tables.

I wanted to be able to count how many ID occurences i have in the other table. I've tried some different forms of CALCULATE with COUNTROWS and/or RELATEDTABLE, but nothing worked.

At the end of the day, if there is any other solution that shows me the list of IDs in both tables, that would work for me.

Some example on what's going on:

The tables are something like this:

Table1
ID            Revenue
123US         100
321DE         200

Table2:
Account       Campaign        ID
XYZ             USA          123US
ABC             USA          123US
ASD             GER          321DE

Sorry about the format. First, i tried to create a relationship between the tables, but as there are multiple values for [ID] in both tables, that wasn't possible. Then, i tried, for example, to add a column in Table1:

=CALCULATE(COUNTROWS('Table2'),'Table1'[ID]='Table2'[ID]))

but that didn't worked.

Edit: added the example and fixed formatting.

Welcome to SO. Please provide a sample of table structure, and the actual formulas you used (with parameters, tables, fields, etc). That will help us help you. — guitarthrower
Thank you very much. Added some information on the question, please let me know if it's clearer now. — IgorKB

guitarthrower guitarthrower · Accepted Answer · 2015-03-31T21:55:55

You are close!

The CALCULATE function takes a table and a filter(s). You correctly put in your table, but in DAX you need to wrap your desired filter in the FILTER function.

On Table1 you would use the following formula to count the items on Table2 that have the same ID

=CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[ID]=Table1[ID]))

On Table2 you would use the following formula to count the items on Table1 that have the same ID

=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[ID]=Table2[ID]))

Counting cells in related table with powerpivot

1 Answers