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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MC99
Frequent Visitor

Table Slicer looks for matching items based on another column in table

I cant figure out how to achieve the following - any help would be great!

My data format is something like this:

Screenshot 2019-03-22 at 18.18.32.png

I have a slicer added on the report for the 'Name' column.

When a user chooses e.g 'title 1' from the slicer i want to generate a bar chart.

What i want is to get all the items from the whole table that match the 'title 1' 's region - i.e 'Region 1' , then create a bar chart that shows the percentages of each Size of the matching items for that Region.

Something like this:
Screenshot 2019-03-22 at 18.20.18.png

 

 

 

 

 

 

 

 

 

I just cant figure out how to do this. anyone ?

Thanks

Mark

8 REPLIES 8
Anonymous
Not applicable

@MC99 

What if you try this:

 

Count = COUNTROWS( Table2 )

Count of all Selected = CALCULATE( [Count], ALLSELECTED(Table2))

% of Selected = DIVIDE( [Count], [Count of all Selected])

Region Selected = SELECTEDVALUE( Table2[Region] )

Region Selected is only if you want to use it in Card that you can put on top of the chart instead of adding Region to the legend

 

Total of Selected and overall.png

Thx Nick - can u share the .pbix ? 

Anonymous
Not applicable

Appreciate the support - that really helped to understand the methodology.

i have given it a try myself on a different data set - but for some reason i cant get it right :

 

1. everytime is select an item in the slicer it filters the dataset to show only the selected item not the returning all the matching 'regions'. 

2. I cant the title added to the chart as u did.

 

I'v uploaded my attempt on page 2 (using a different table) of the following powerbi file:

https://drive.google.com/file/d/1p-sxgQhTzurotsGv4L73Z2MIYP8n7v15/view?usp=sharing

 

regards

Mark

 

Anonymous
Not applicable

Mark,

Took a quick look, and here's what I got.  Region (which looks like is coming from Table1) has no relationship to the Results table, so a filter on Region will not propagate to Table1, which is why nothing happens. You would need to either add a column to the Results table indicating what region, or create a new Dimension table that would have Cluster in one Column and Region in another column:

New Dim Table.pngData Model.png 

 

Then you can add the Region Column from the new DimClusterRegion table as a filter and change the measure from selected region from table to 

Region Selected = SELECTEDVALUE( DimClusterRegion[Region], "Multiple Regions")

That should filter, hopefully, like you expect. I have attached the pbix below for you to mess around with.

 

And regarding the chart title, what is actually happening there is I'm using the Region Selected Measure in a Card Visualization and just puting at the top of the chart. Maybe there's a better way, but that way works for me.

 

Hope that helps

 

File:

https://1drv.ms/u/s!Amqd8ArUSwDS0Ety-ZS__4DuP11O

 

in my example i am not trying to use the 'region' anymore but rather created a completely new table 'Results'.

Then i am trying to do exactly as we did before:

 

1. Use the slicer to select a 'Factory'

2. The selected 'Factory has a corresponding column in its row for 'Cluster' - e.g Factory=Name15 's matching cluster is 'Australia'

3. i then want to take the Cluster for that selected factory ('Australia') and go get all the rows in the 'Results' table that  have a matching 'Cluster' (there are 9 rows that have a cluster = "Australia").

4. Once i have those i am then creating the chart/table as you did for region in the previous example. i.e look at the percentages for each answer for Q1 based on the the returned matching clusters rows. ( <5million = 5/9  ; 5-10million = 3/9 ; >10 million = 1/9 

 

Screenshot 2019-03-26 at 10.22.57.png

sorry if confusing.

rgds + thx so much for the help.

Mark

Anonymous
Not applicable

think I maybe some-what understood Smiley Happy

 

Here's the table I get when I use Factory = Name 15  and Year = 2018

Final Table.png

 

Here's the code :

 Total Clusters = 
 CALCULATE( 
     [Cluster Count], 
     CALCULATETABLE( 
         FILTER( ALL( Results), 
         VALUES(Results[Cluster]) = [Cluster Selected])
        )
)

Cluster % of Selected = DIVIDE( [Cluster Count], [Total Clusters])

Cluster Count = COUNTROWS( Results )

Maybe what you had in mind, or maybe at least close?

i think we getting closer.

The result for Factory: Name 15 and year 2018 should be:

The seleted cluster is 'australia'

<5 million     -  5  -  9   ( there are 5 factorys in cluster Australia with this value)

5-10 million  -  3   - 9   ( there are 3 factorys in cluster Australia with this value)

>10 million   -  1  -  9   ( there is 1 factory in cluster Australia with this value)

 

i think your example code is having the same issue i couldnt crack - that it is filtering by the Name15 factory and hence only showing the  <5 million option in your table - when it should be showing the results of all factories that are in that cluster 'australia'.

 

I'll keep trying - let me know if you come up with a solution. 

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.