Forum Discussion
Create a Slicer
Hi everyone,
I would be glad to have some help!
I have 3 tables.
--Farm
id_farm
farm_name
--Flock
id_flock
id_farm
--Animal
id_animal
id_flock
---Relationships---
Farm - Flock
Flock - Animal
I would like to create a slicer that allow me to choose and show the farm's quantity by the choise below.
up to 10 Animals
from 10 to 50 Animals
from 50 to 100 Animals
from 100 to 200 Animals
For example if I choose from 100 to 200 animals it shows me the farm's quantity number in a card.
Thanks !
Wneiton
wneiton - One possibility is to create a new Calculated Table and Calculated Column:
1. Calculated Table for the new filter (Note that it has a buffer to go up to 1,000):
Animal Counts = var __NumberList = GENERATESERIES(1, 1000, 1) return ADDCOLUMNS( __NumberList, "Farm Quantity Bucket", SWITCH( TRUE(), [Value] < 10, "up to 10 Animals", [Value] < 50, "from 10 to 50 Animals", [Value] < 100, "from 50 to 100 Animals", [Value] < 200, "from 100 to 200 Animals", "more that 200 Animals" ) )2. Create a new Calculated Column on the Farm table:
Farm Quantity = var __RowCount = COUNTROWS(RELATEDTABLE(Animal)) return MIN(__RowCount, 1000) //In case a farm has more than 1,000 animals, we still need the relationship to work.
3. Create a Relationship between the new table and the new column.
Now, you can filter on the new "Farm Quantity Bucket" to show only farms of certain sizes.
Hope this helps,
Nathan
8 Replies
wneiton - One possibility is to create a new Calculated Table and Calculated Column:
1. Calculated Table for the new filter (Note that it has a buffer to go up to 1,000):
Animal Counts = var __NumberList = GENERATESERIES(1, 1000, 1) return ADDCOLUMNS( __NumberList, "Farm Quantity Bucket", SWITCH( TRUE(), [Value] < 10, "up to 10 Animals", [Value] < 50, "from 10 to 50 Animals", [Value] < 100, "from 50 to 100 Animals", [Value] < 200, "from 100 to 200 Animals", "more that 200 Animals" ) )2. Create a new Calculated Column on the Farm table:
Farm Quantity = var __RowCount = COUNTROWS(RELATEDTABLE(Animal)) return MIN(__RowCount, 1000) //In case a farm has more than 1,000 animals, we still need the relationship to work.
3. Create a Relationship between the new table and the new column.
Now, you can filter on the new "Farm Quantity Bucket" to show only farms of certain sizes.
Hope this helps,
Nathan
Anonymous many thanks.
Look at the picture below. When I select from 400 Animals to 800
It's showing 18 farms, but it is counting animal out of this range, it is showing for example farms that has less than 400 animals.
Animal_Counts = var __NumberList = GENERATESERIES(1; 1000; 1) return ADDCOLUMNS(__NumberList;"Farm Quantity Bucket";SWITCH(TRUE();[Value] < 10; "up to 10 Animals";[Value] < 50;"from 10 to 50 Animals";[Value] < 100;"from 50 to 100 Animals";[Value] < 200;"from 100 to 200 Animals";[Value] > 200 && [Value] < 400;"from 200 to 400 Animals";[Value] > 400 && [Value] < 800;"from 400 Animals to 800";"more that 800 Animals"))wneiton - Did you do steps 2 and 3? Also, in the measure, you will miss the exact values 200, 400 because of the less than and greater than signs. You don't need to check the greater than, because the earlier checks already established that.
Cheers,
Nathan
