topic Re: Data Table Creation with FILTER DISTINC and COUNT in DAX Commands and Tips

Data Table Creation with FILTER DISTINC and COUNT

MackenzieGENUS — Thu, 11 Apr 2024 22:11:33 GMT

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

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?

Re: Data Table Creation with FILTER DISTINC and COUNT

vicky_ — Fri, 12 Apr 2024 06:03:31 GMT

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

Re: Data Table Creation with FILTER DISTINC and COUNT

Anonymous — Mon, 15 Apr 2024 03:24:23 GMT

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.