Count in two related tables

[hector3315]

Hi I'm new here and I don't know if this is the place to this issue. If not let me know it and i will change it from here.

The fact is that I have 2 related tables. In one i have an id and in the other the reference of that id.

They are similar to these 2 (I can't show the real ones because of privacity issues. I hope these 2 will be ok to explain myself):

I need to add a column to table1 with the count of the number of tasks that each id has.

I have the following dax fórmula but it returns the count of all the tasks of the table 2 at each row of table 1. That is for this example 4 in all the rows of the new column in table 1. And I want the values 3,1...

totalTareas = COUNTAX(FILTER(table2;RELATED(table1[ID]) = table2[Id_del_proceso]);table2[TASK])

where id_del_proceso is table1_id in this example

I'm new with dax functions and I don't know how to solve it, neither why I obtain that result, but this in sql would be something like a join between the 2 tables and a count of the task grouped by the table1_id.

Please clould you explain me what I'm doing wrong and how could i solve this ?

Thanks for the help

[Baskar]

Cool,

I suggest two ways.

1. Create calculated column on Table 1

Column =Calculate( COUNTROWS(RELATEDTABLE('Table 2')))

2. Create measure .

for that u have to create relationship between these two tables . then create measure 

Measure = countA('Table 2' Table_ID)

let me  know if any help

[hector3315]

Thanks for answer me but that is not what i'm looking for. I don't want a measure. I need to know the count of taskt that each id has. And i want that as a column to print each one in each row.

With your first advice i get the count of all. the same that with my first formula.

What i didn't know is that CALCULATE exists i think that is what i need i will try it and i will say if i get something.

[Anonymous]

@hector3315,

Since you have related the two tables, the following measure (not column) should do what you need:

totalTareas = COUNT( table2[TASK] )

For some guidance on measures vs columns, check the following links which I 've found useful:

http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

https://www.sqlbi.com/tv/dax-calculated-columns-vs-measures/

[hector3315]

No,  i explained myself in a bad way i need to print table 1 and i want the count of all the tasks each id has. Not a global count, that is why i need that column i cheked the links and are nice but in this case i need the calculated colum.

Thanks for the information about measures and columns i found it interesting 🙂

[Anonymous · Accepted solution]

@hector3315,

If you drag a Matrix visual to your canvas, with ID in Rows, and the measure totalTAreas in Values, I think it will give you the result you need.

Or you can add a column as described by @Baskar, though I can't see from your requirements why it must be a column.

Note that columns can't be used is some situations (e.g. axis on a scatter plot), measures are generally more efficient, but measures can't be used as slicers.

[hector3315]

That works so fine man !!

I didn't know that measures could be used to get values by row I thougth that they were only to get one value.

I have just seen the video and now I understand how a measure works. And yes it is more efficient than calculated columns by far.

I found a bad way to do it duplicating the table and grouping by id but that is so unefficient.

A lot of thanks to both and sorry cause the answer was here from the first message but i didn't know how to apply it. Sometimes it is better no to run a lot and take time to see the things clare.

[Anonymous]

I'm glad we helped.  I too didn't understand the simple power of measures and a good data model until I'd used them a few times on my own datasets.

[Baskar]

1. create relationship between these two table.

2. Create calculated column on Table 1

Column =Calculate( COUNTROWS(RELATEDTABLE('Table 2')))

am sure it will help u, if not let me know i will help u my friend .

[hector3315]

The second solution doesn't work. I  go to investigate that CALCULATE. i think that is the solution to my problem