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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jengwt
Helper V
Helper V

get Pie Chart to Filter Table

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
9 REPLIES 9
jengwt
Helper V
Helper V

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

v-yuta-msft
Community Support
Community Support

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

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

MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



*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

Hi @jengwt,

 

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

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



*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

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.