Forum Discussion
Generating a calculated column. by grouping column in another table based on id
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!
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.
6 Replies
- selimovd
Most Valuable Professional
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 regardsDenisBlog: WhatTheFact.biHi 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 regardsDenisBlog: WhatTheFact.bi
