Forum Discussion

hector3315's avatar
hector3315
Frequent Visitor
9 years ago
Solved

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

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

9 Replies

    • hector3315's avatar
      hector3315
      Frequent Visitor
      9 years ago

      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's avatar
        Baskar
        Icon for Resident Rockstar rankResident Rockstar
        9 years ago

        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 .

         

  • Baskar's avatar
    Baskar
    Icon for Resident Rockstar rankResident Rockstar
    9 years ago

    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's avatar
      hector3315
      Frequent Visitor
      9 years ago

      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.