cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## 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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 4
Anonymous
Not applicable

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

Resolver I

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
Not applicable

Hi @wneiton

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

Thanks

Raj

Resolver I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.