Forum Discussion

MackenzieGENUS's avatar
MackenzieGENUS
New Member
2 years ago
Solved

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?

 

COUNT
DAX
Distinct
Filter
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.