Data Table Creation with FILTER DISTINC and COUNT

[MackenzieGENUS]

I have a table A:

ID      AGEMO     BINDER

1        1                   0

2         1                  1

3       6                    0

4         8                  0

8       21                   0

and I created a second table (TABLE B) from the following command:

ID Target = distinct('Table A'[AGEMO])

This creates this Table B:

AGEMO

1

6

8

21

In column B I need to add an additional column that counts the ID's from table 1 that correspond to each AGEMO but only to do that if BINDER=0 to give me the following in Table B:

AGEMO    COUNT ID

1                 1

6                 1

8                 1

21               1

I was using this formula to do that:

COUNT AGEMO = countrows(filter(relatedtable('TABLE A'),'TABLE A'[BINDER]=0))

But instead of giving me the table I want above its giving me the following table:

AGEMO   COUNTID

1              4

6              4

8               4

21              4

Where 4 is just the total number of BINDER=0 animals in TABLE A instead of relating them to the actual AGEMO they should be with.

Can someone help me with this?

[v-jiewu-msft · Accepted solution]

Hi @MackenzieGENUS ,

First of all, many thanks to @vicky_  for your very quick and effective replies, and I will give some additions below:

1.Create the simple table.

2.Use the distinct dax to create the Table B.

Table B = distinct('Table'[AGEMO])

3.Create the relationship between the Table and Table B.

4.Create the new column to count the ID from Table.

COUNT ID = CALCULATE(COUNTROWS('Table'), 'Table'[BINDER] = 0)

5.Drag the column into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vicky_]

If you haven't created a relationship between table A and table B, then that will happen. 
I would also suggest just creating the table with the countID column already included - 
try changing your table constructor to something like:

ID Target = ADDCOLUMNS(distinct('Table A'[AGEMO]), "COUNT ID", CALCULATE(COUNTROWS('Table A'), TABLE A'[BINDER]=0))