cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hector3315
Frequent Visitor

Count in two related tables

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

Captura.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Steve_Wheeler Solution Sage
Solution Sage

Re: Count in two related tables

@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.

View solution in original post

9 REPLIES 9
Steve_Wheeler Solution Sage
Solution Sage

Re: Count in two related tables

@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/

Baskar Resident Rockstar
Resident Rockstar

Re: Count in two related tables

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
Frequent Visitor

Re: Count in two related tables

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 🙂

 

 

 

 

Baskar Resident Rockstar
Resident Rockstar

Re: Count in two related tables

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
Frequent Visitor

Re: Count in two related tables

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.

hector3315
Frequent Visitor

Re: Count in two related tables

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

Steve_Wheeler Solution Sage
Solution Sage

Re: Count in two related tables

@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.

View solution in original post

hector3315
Frequent Visitor

Re: Count in two related tables

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. Smiley Happy

Steve_Wheeler Solution Sage
Solution Sage

Re: Count in two related tables

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors