join information from different tables

[wneiton]

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

[Anonymous · Accepted solution]

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

[wneiton]

Hi @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!

[Anonymous]

Hi @wneiton

Pull colun which you have created in step#4 and a measure COUNT(Farm[FarmID]) will give you that solution.

Thanks

Raj

[wneiton]

Hi @Anonymous

As you had suggest me I created this Column on Animal table:

Options = IF ( [Conta_Animal] < 100 ; "Upto 100 Animals"; IF( [Conta_Animal] >= 100 && [Conta_Animal] < 200 ;"100-200 Animals";"Above 200 Animals"))

I didn't understand, sorry!

What I want is:

Options to choose (slicer)

Up to 100 animals

From 100 up to 200 animals

Above 200 animals

Then, it shows a simple value with a Farms' Quantity in a card

 

thanks 

Wneiton