Forum Discussion

huguest's avatar
huguest
Advocate II
8 years ago
Solved

LAX count values where column b = column a

Hello,

I have a Table that contains information about Parent and Child records together (ID, Parent_ID, Type, Gender).  I need to add 2 columns that calculate the number of children of each gender for each parent (Boy, Girl) but cannot figure out how to do it.

 

IDParent_IDTypeGenderBoyGirl
11Parent 31
22Parent 22
31ChildBoy  
41ChildBoy  
51ChildBoy  
61ChildGirl  
72ChildBoy  
82ChildBoy  
92ChildGirl  
102ChildGirl  

 

Thank you,

Hugues.

Need Help
  • nickchobotar's avatar
    nickchobotar
    8 years ago

    Got it huguest

     

    Here you go

     

    Boy = 
IF (
    Table1[Type] = "Parent", 
    CALCULATE (
        COUNT ( Table1[Gender] ),
        Table1[Gender] = "Boy",
        ALLEXCEPT ( Table1, Table1[Parent_ID] )
    )
)

Girl = 
IF (
    Table1[Type] = "Parent", 
    CALCULATE (
        COUNT ( Table1[Gender] ),
        Table1[Gender] = "Girl",
        ALLEXCEPT ( Table1, Table1[Parent_ID] )
    )
    )
    
)

5 Replies

  • nickchobotar's avatar
    nickchobotar
    Skilled Sharer
    8 years ago

    huguest

     

    Do you expect to get the results in the same table in a calculated column, or it's ok to use a measure and see your results like shown below?

    Parent_IDBoyGirl
    131
    222

     

    N-

    • huguest's avatar
      huguest
      Advocate II
      8 years ago

      Ideally as a calculated column in the same table.

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        8 years ago

        huguest

         

        Hi, try with this calculated column

         

        Boy =
IF (
    Table3[Type] = "Parent",
    COUNTROWS (
        FILTER (
            Table3,
            Table3[Parent_ID] = EARLIER ( Table3[Parent_ID] )
                && Table3[Gender] = "Boy"
        )
    )
)

        Regards

         

        Victor

        Lima - Peru