Generating a calculated column. by grouping column in another table based on id

[Anonymous]

Hi All,

I have 2 tables A and B. Table A has coloumns peopleID, employed, social, pension and other. Table B has coloumns peopleID and houseID. One houseID can have multiple peopleID. A many to one relationship exists between table B and table A on peopleID. I want a calculated coloumn in table B based on if for a particular houseID :

if employed, pension and other are blank and social not blank then Social

if pension,other,social are blank and employed not blank then employed

if employed is blank and social,pension and other are not blank then social,pension and other

and so on. 

Now, since one house id can have multiple people id I want these conditions to be tested for all people id for that house id.

Any clue how to do it?

Thanks in advance!

[selimovd]

Hey @Anonymous ,

can you upload a few (fake) rows to that tables and post them here? Also show us how the result should look like.

This makes it easier to help you with the calculation.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Hi @selimovd 

First Screenshot of table A, 2nd screenshot of table B

For example occupancy id 10000001 has 4 people id 13542,13543,13544 and 13545. But the information is avilaible for only first 3, combining the rows for first 3 in the first table the incomegroup for the occupancy should be SocialWelfare and Employed 

[selimovd]

Hello @Anonymous ,

to get the last example try the following:

Amount Employed with SocialWelfare =
CALCULATE(
    COUNTROWS( TableA ),
    TableA[SocialWelfare] = "SocialWelfare" && TableA[Employed] = "Employed"
)

Is that what you meant?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Hi @selimovd 

No, What I mean is I need a coloumn called income group against each occupancy id. For example for the occupancy id highlighted in yellow rather than multiple rows with different groups, I want one row with "Employed and Social Welfare"

Thanks!

[selimovd]

Hey @Anonymous ,

you said the tables have a many to one relationship.
This would mean the left table is the one side, the right table on the many side.

Now on the right side if we take employee 13542 one row has the value from "SocialWelfare" and one row the value from "Employed". How should be the decision which value is taken?

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous · Accepted solution]

Hi @selimovd ,

I have figured out a simpler solution for this now. Just using CONCATENATEX ( VALUES ( vOccupantFullDetails[Income group]),vOccupantFullDetails[Income group],",")

Thanks for your help though.