Forum Discussion

huguest's avatar
huguest
Icon for Advocate II rankAdvocate 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 f...
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] )
    )
    )
    
)
nickchobotar's avatar
nickchobotar
Icon for Skilled Sharer rankSkilled 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
Icon for Advocate II rankAdvocate II
8 years ago

Ideally as a calculated column in the same table.

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity 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

  • nickchobotar's avatar
    nickchobotar
    Icon for Skilled Sharer rankSkilled Sharer
    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] )
    )
    )
    
)
    • huguest's avatar
      huguest
      Icon for Advocate II rankAdvocate II
      8 years ago

      Thank you, I will give it a try as soon as I get a chance.