Reply
Member
Posts: 56
Registered: ‎02-13-2018

get Pie Chart to Filter Table

[ Edited ]

Hello, I've got a pie chart that , from two counting Measures, displays the number of client sites that have or have not been called in a time period. Next to it, I have a table that displays stats about the sites, including how many times they have been called in the time period.

 

I would like for the table to be filtered when the user clicks on a section of the pie chart, but I can't get it to work. PBI acts like it's trying to do something (screen flashes, table refreshes), but it doesn't actually filter the table.

 

This is a redacted example of that the page looks like:

calldetailsredacted.png

scrnsht2.PNG

SiteName = "Profile" (sorted by SiteID)

SiteOwner = "Owner"

Calls per Site = "Calls"

Date = "Call Date" (date slicer)

Here is the code for the two metrics that count the number of "Sites Called" and "Sites Not Called":

 

[measure] Calls Per Site = COUNT('Calls'[CallID])

 

[measure] Sites Called = CALCULATE(COUNT('Sites'[SiteID]), FILTER('Sites', [Calls Per Site] >= 1))

 

[measure] Sites Not Called = CALCULATE(COUNT('Sites'[SiteID]), FILTER('Sites', [Calls Per Site] < 1))

 

Here is some sample data of what is contained in the tables:

Sites table:

SiteIDSiteNameinitsO MktD Mkt
Site1Happy Apple Makers1ac
Site2The Most Interesting Man in the World2ba
Site3Sheep Sheerers3db
Site4Grub's Grub Restaurant4bc
Site5Golum's Precious Finders5c

d

 

Calls:

CallIDCallerSiteIDDate
c1bobSite14/1/2018
c2joeSite34/1/2018
c3sallySite24/1/2018
c4ahmedSite24/2/2018
c5schmidtSite24/1/2018
c6nereeseSite14/1/2018
c7juanitaSite34/2/2018
c8oscarSite24/2/2018
c9ivanSite14/1/2018
c10joeSite14/1/2018
c11ahmedSite14/2/2018
c12joeSite24/4/2018
c13juanitaSite14/1/2018
c14ahmedSite34/2/2018
c15oscarSite24/1/2018
c16sallySite14/2/2018
c17schmidtSite24/1/2018
c18juanitaSite14/2/2018
c19oscarSite24/2/2018
c20juanitaSite14/1/2018

 

Emploees:

Employeeinits
juanita1
joe2
ahmed3
schmidt4
oscar5
bob6
sally7
nereese8
ivan9
Super User
Posts: 1,477
Registered: ‎09-19-2016

Re: get Pie Chart to Filter Table

Hi @jengwt,

 

Can you share a sample of your data? from the image is difficult to understand what is the data setup behing it.

 

Thank you.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Community Support Team
Posts: 742
Registered: ‎02-06-2018

Re: get Pie Chart to Filter Table

Hi jengwt,

 

Modify your DAX formula like this and try again:

Sites Called =
CALCULATE (
    COUNT ( 'SitesTable'[SiteID] ),
    FILTER ( ALLSELECTED ( 'SitesTable' ), [Calls Per Site] >= 1 )
)

Sites Not Called =
CALCULATE (
    COUNT ( 'SitesTable'[SiteID] ),
    FILTER ( ALLSELECTED ( 'SitesTable' ), [Calls Per Site] < 1 )
)

Regards,

Jimmy Tao

Member
Posts: 56
Registered: ‎02-13-2018

Re: get Pie Chart to Filter Table

[ Edited ]

*edited OP to include this*

@MFelix Is this helpful to you? Again, I've had to make some redactions.

Compared to the screenshot of the report page:

SiteName = "Profile" (sorted by SiteID)

SiteOwner = "Owner"

Calls per Site = "Calls"

Date = "Call Date" (date slicer)

 

data_examplecropped.png

Super User
Posts: 1,477
Registered: ‎09-19-2016

Re: get Pie Chart to Filter Table

Hi @jengwt,

 

This is helpfull but I also need the data values can you send a small table with that informaiton.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Member
Posts: 56
Registered: ‎02-13-2018

Re: get Pie Chart to Filter Table

[ Edited ]

@MFelix I suppose I could makes you some sample tables of what the data looks like. How can I get them to you / attach them to the OP?

Member
Posts: 56
Registered: ‎02-13-2018

Re: get Pie Chart to Filter Table

Sorry, @v-yuta-msft but not only does that not solve the problem, it basically does the exact same thing as my code.

Super User
Posts: 1,477
Registered: ‎09-19-2016

Re: get Pie Chart to Filter Table

You can add them to the post as text a e-mail to a onedrive/we tranfers file or send me a Private message with the information.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
Member
Posts: 56
Registered: ‎02-13-2018

Re: get Pie Chart to Filter Table

[ Edited ]

*edited OP again*

@MFelix I just made up some sample data, and a new PBI Report to go with it so that I can post scrnshts directly from there.

Ok, so technically there is a third table involved here, listing employees and their attributes. I didn't think it was necesary, but I might as well include it.

As you can see, site "ownership" is determined by INITs, but the owner may not necesarily be the person who calls their site. You can see in the screenshot how they are related.

 

Sites table

SiteIDSiteNameinitsO MktD Mkt
Site1Happy Apple Makers1ac
Site2The Most Interesting Man in the World2ba
Site3Sheep Sheerers3db
Site4Grub's Grub Restaurant4bc
Site5Golum's Precious Finders5cd


Calls table 

CallIDCallerSiteIDDate
c1bobSite14/1/2018
c2joeSite34/1/2018
c3sallySite24/1/2018
c4ahmedSite24/2/2018
c5schmidtSite24/1/2018
c6nereeseSite14/1/2018
c7juanitaSite34/2/2018
c8oscarSite24/2/2018
c9ivanSite14/1/2018
c10joeSite14/1/2018
c11ahmedSite14/2/2018
c12joeSite24/4/2018
c13juanitaSite14/1/2018
c14ahmedSite34/2/2018
c15oscarSite24/1/2018
c16sallySite14/2/2018
c17schmidtSite24/1/2018
c18juanitaSite14/2/2018
c19oscarSite24/2/2018
c20juanitaSite14/1/2018

 

Employees

Employeeinits
juanita1
joe2
ahmed3
schmidt4
oscar5
bob6
sally7
nereese8
ivan9

scrnsht2.PNG

Member
Posts: 56
Registered: ‎02-13-2018

Re: get Pie Chart to Filter Table

[ Edited ]

Ok so I now know what the problem is: The pie chart displays Measures and does not pull from columns/data. Therefore, it doesn't have a way to relate back to the table, even though the Measures are based off of the same data as the table.

 

So, instead of using the measures, I have to fnd a way to use the Sites in the pie chart that are sill responsive to the date slider, which does not seem to be possible. Please help!

 

In other words, I need to make a calculated column that recognizes data slicing on a give page.

 

Figure no one's going to see this post any more. Made a new one, aligned to the problem: http://community.powerbi.com/t5/Desktop/Create-Calculated-Column-that-Responds-to-Date-Slicer/td-p/4...