Forum Discussion
join information from different tables
Hi, I am learning PBI and I would be glad to have some help.
I have 3 tables in relationships in PBI as you can see below.
What I need to know is how to get the amount of animal each Farm has.
Do I need to create a new table? or a new column ?
As a result I want to create 3 options for user to choose.
e.g:
Amount of Farms up to 100 animals
Amount of Farms from 100 to 200 animals
Amount of Farms above 200 animals
Farm |
id_farm |
farm_name |
Flock |
id_flock |
id_farm |
Animal |
id_animal |
id_flock |
Hi wneiton
1. Set the relationship between these tables.
Farm -> Flock ( One to Many, Filter direction is from Farm to Flock)
Flock -> Animal ( One to Many, Filter direction is from FLock to Animal)
2. Create a new measure for Animal count = COUNT( Animal[AnimalID])
3. Pull Farm Name ( From Farm Table), and Animal count measure to the visual.
4. You can use this formul for the buckt option : IF ( Animal count < 100 , "Upto 100 Animals", IF( Animal count >= 100 && Animal count < 200 ,"100-200 Animals","Above 200 Animals")) and create a slicer based on this.
Thanks
Raj
4 Replies
Hi wneiton
1. Set the relationship between these tables.
Farm -> Flock ( One to Many, Filter direction is from Farm to Flock)
Flock -> Animal ( One to Many, Filter direction is from FLock to Animal)
2. Create a new measure for Animal count = COUNT( Animal[AnimalID])
3. Pull Farm Name ( From Farm Table), and Animal count measure to the visual.
4. You can use this formul for the buckt option : IF ( Animal count < 100 , "Upto 100 Animals", IF( Animal count >= 100 && Animal count < 200 ,"100-200 Animals","Above 200 Animals")) and create a slicer based on this.
Thanks
RajHi Anonymous
Very good answer. It helped me a lot.
now, I have the number of animals each farm has
But I still need to know how many farms has up to X animals.
e.g
up to 100 animals 10 farms
from 100 to 500 animals 25 farms
I want to show the farms' number on card, for example.
Thanks!
Hi wneiton
Pull colun which you have created in step#4 and a measure COUNT(Farm[FarmID]) will give you that solution.
Thanks
Raj
