Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wneiton
Resolver I
Resolver I

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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

 

 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

@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. 

 

 

 

farm_quantity.JPG

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"))
Anonymous
Not applicable

@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

@Anonymous  - Yes I did step 2 and 3. 

 

relationships.JPG

 

I don't know why its showing 14 farms and not only 3 as it should be.

quantity.JPG

 

Wneiton | thanks again

Anonymous
Not applicable

Check the relationship to make sure "Animal Counts" is filtering "Farm" and not the other direction. The arrow should point at Farm.

@Anonymous Loot at, I think its right!

 

relationships2.JPG

Anonymous
Not applicable

@wneiton  - Is it working now?

@Anonymous Yes, it's working now after I've removed a report level filter. 
I had filtered some flock's name from flock's table on report level filter, but it seems not to work with my slicer.

 

cheers

thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.