Filter Pie Chart based on Type from table

[Aragorn]

Hi,

 I have 3 Visuals in my report. 
 a Tree Map, a Table and a Pie Chart.

Tree Map has the TYPES as categories.

So the data is a buch of objects with an ID, Type and a Status. The Pie Chart is set to "Filter" from the other 2 visuals. 

When I click on a item in the Tree Map (Object Type), The Pie chart filters the data to show only for that Object Type.

When I click on an item from the Table however, It filters only that object and it'll say 1 object.

Is there a way to Filter the Pie Chart to show data about the "Object Type" even when you click on an item from the table?

ex:
 Object Type - Gold has 3 Critical, 5 Fair, 4 Good and 3 Poor.

When you select an item from the table that has GOLD as object type, the Pie chart still should show : 3 Critical, 5 Fair, 4 Good and 3 Poor instead of saying 1

I've attached the report as a sample. 
Please help!

Pbix File:

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

[MFelix]

Hi  @Aragorn ,

Create a table with Status then add the following metric:

Status Total = CALCULATE(COUNTROWS('Table'), 'Table'[STATUS] = SELECTEDVALUE('Table 2'[STATUS]))

[Aragorn]

Hi @MFelix ,

 Thank you so much. This really works somehow. But, my STATUS column is a custom column which is derived by a measure. 

Basically it takes a Value and Determines a status for that value. When I add this column, The pie chart stops working... 😞 Goes back to selecting '1' again. 
How can I fix it? 

I've attached the file below.

Pbix File 

Thank you so much!

-K

[MFelix]

Hi @Aragorn ,

This has to do with context, when you add in the column with the status then the filter get's again impacted by that column try the following measure:

Status Total = 
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        'Table'[TYPE] in DISTINCT( 'Table'[TYPE] )
            && 'Table'[STATUS] = SELECTEDVALUE ( 'Table 2'[STATUS] )
    )
)

[Aragorn]

Hi @MFelix ,

 This further confirms that I dont undestand the concept of Context at all. 😞

Im sorry to keep pushing you on this. I promise this is the last time. 🙂

My data comes from different sources, and there are relationships. When I use your measure, it gives false values when filtering through other visuals.

 Check this image:

When I click a level in the bar chart, the table shows correct data, but the pie chart shows false values. 😞 I really dont understand why this is happening. 

Also, I should be able to use "CTRL" and select multiple filters. ex: GOLD + CRITICAL

and the table should display Critical Objects in the type GOLD. This doesnt work with a disconnected table 😞

Can you please help with this pleeeeeeeease?

Pbix File 

Regards,

Aragorn 
 

[MFelix]

Hi @Aragorn ,

Once again context, the original request was to get the selection from the table to filter out the pie chart, using other interactions in this case the location changes the context. In this case when you select the Bar chart what do you want to happen to the pie chart to be highligth? Should it only show or the selected status or for all status?

If you disconnect the status table and add the Status from the table object to the bar chart and then redo the measure to this:

 COUNTROWS (
    FILTER (
        ALL ( ObjectTbl ), ObjectTbl[TYPE] in DISTINCT( ObjectTbl[TYPE] )
            && ObjectTbl[STATUS] = SELECTEDVALUE ( StatusTbl[STATUS] ) && ObjectTbl[Location] in DISTINCT(ObjectTbl[Location])
    )
)

should work has needed:

Please tell me if this is what you need or what is the result you should get when selecting the bar.

[Aragorn]

Hi @MFelix ,

In the latest file you gave, the pie chart doesn't filter the table. Is this something impossible?

Regards,

K

[MFelix]

Hi @Aragorn ,

Is it possible for you to give an example of the final result, for example selecting gold should return X values on the bar chart and X values on bar chart, selecting poor/garage on the bar chart should retun x on table and x on pie chart.

[Aragorn]

Hi @MFelix ,

 Thank you for replying! I thought you've given up! 😮

ok i'll explain.

1. When you click on GOLD in the tree map, 

  -The Table should FILTER to show only the objects in GOLD type

  -The Pie Chart should FILTER to show only the objects in GOLD type

  -The Bar Chart should HIGHLIGHT the GOLD objects in all locations

2. When you click on an Object with GOLD form the table,

  -Nothing happens to the Tree map. (Great if GOLD can be highlighted, but I dont think thats possible)

  -The Pie Chart should show info about GOLD object type. (Not Indivudual Object)

  -The Bar Chart HIGHLIGHTS that object

3. When you click on an Item in the Bar Chart (ex: GARAGE > CRITICAL)

  -The Tree Map will HIGHLIGHTED by the selected object types

  -The Table will FILTER the Critical Objects in that location

  -The Pie Chart will HIGHLIGHT the Critical Objects in the Garage out of all objects

4. When you click GOLD in the Tree map, and CTRL + CLICK on Critical in the Pie Chart,

  - The table should FILTER the Critical Objects in the GOLD Object Type

  - The Bar Chart will Hightlight those objects

5.  When you click on Garage > Critical in the Bar Chart and CTRL + CLICK on Critical in the Pie Chart,

  - The Table should FILTER the Critical Objects in the Garage

Those are all the requirements. 

Thank you so much for your support!


Regards,

K

[MFelix]

Hi @Aragorn,

I have not given up but have some busy days, I will work on this later today so my answer will not be immediate OK?

But I will not forget about this request. 

[Aragorn]

Hi @MFelix ,
  Small update, dont know whether this will make things any different.

But the STATUS column is now a raw column. Its no longer a custom column.

Thank you and Regards,

K

[MFelix · Accepted solution]

Hi @Aragorn ,

Believe I have made it work has you need can you please check the file below.

Metric was updated to:

Status Total = var MAXXX = MAX(ObjectTbl[STATUS])
Return
IF(ISFILTERED(ObjectTbl[Location]),
   COUNTROWS (
    FILTER (
        ALLSELECTED(  ObjectTbl ), ObjectTbl[TYPE] in DISTINCT( ObjectTbl[TYPE] )
            && ObjectTbl[STATUS] = SELECTEDVALUE(StatusTbl[STATUS] ) && ObjectTbl[Location] in DISTINCT(ObjectTbl[Location])
    )
) ,
    COUNTROWS (
    FILTER (
        ALL ( ObjectTbl ), ObjectTbl[TYPE] in DISTINCT( ObjectTbl[TYPE] )
            && ObjectTbl[STATUS] = SELECTEDVALUE ( StatusTbl[STATUS] )
    )
))

Can you please check and get back to me if any is missing.

[Aragorn]

Hi @MFelix ,

 This and the Filter measure works like Magic!!

Thank you sooooooooooooooooooooooooooooooo much for your time and effort! This helped me alooooot!! Thank you!
🙂 🙂

Regards,
K

[Aragorn]

Hi @MFelix ,

 Thank you again.

but this doesn't work

4. When you click GOLD in the Tree map, and CTRL + CLICK on Critical in the Pie Chart,

  - The table should FILTER the Critical Objects in the GOLD Object Type

  - The Bar Chart will Hightlight those objects

in General, when you select a Status from the Pie Chart, the table isn't filtered. Ex: Select CRITICAL and the table should Filter and show only the critical objects..?

Thanks Regards,

K

[MFelix]

Hi @Aragorn ,

Add the following measure to you model:

Filter measure = countrows(FILTER(ObjectTbl, ObjectTbl[STATUS] in VALUES(StatusTbl[STATUS])))

Now use this has a filter on the table with Is not blank option selected.

[Aragorn]

Hi @MFelix ,

 Thank you sooo much! 🙂

[Aragorn]

Hi @MFelix ,

 Thank you again.

When you select an element in the bar chart, the Pie chart should Highlight that status level Depending on what is selected in the tree map.  

for example, 

 if no object type is selected from the tree map, and you select POOR from the bar chart, the pie chart should highlight the Poor set out of the total dataset,

if GOLD is selected from the tree map, and you select POOR from the bar chart, the pie chart should highlight the Poor Set out of the GOLD object type.

Hope you understand what i'm trying to say...

Again, thank yo so much! 

[MFelix]

Hi @Aragorn ,

The file link is not working can you please reshare.

[Aragorn]

Hi @MFelix ,

 Sorry about that..

Pbix File