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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MackenzieGENUS
New Member

Data Table Creation with FILTER DISTINC and COUNT

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?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

vjiewumsft_0-1713151279242.png

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.

vjiewumsft_1-1713151315617.png

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.

vjiewumsft_2-1713151350564.png

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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.

vjiewumsft_0-1713151279242.png

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.

vjiewumsft_1-1713151315617.png

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.

vjiewumsft_2-1713151350564.png

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

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))

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.