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.
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:
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:
SiteID | SiteName | inits | O Mkt | D Mkt |
Site1 | Happy Apple Makers | 1 | a | c |
Site2 | The Most Interesting Man in the World | 2 | b | a |
Site3 | Sheep Sheerers | 3 | d | b |
Site4 | Grub's Grub Restaurant | 4 | b | c |
Site5 | Golum's Precious Finders | 5 | c | d |
Calls:
CallID | Caller | SiteID | Date |
c1 | bob | Site1 | 4/1/2018 |
c2 | joe | Site3 | 4/1/2018 |
c3 | sally | Site2 | 4/1/2018 |
c4 | ahmed | Site2 | 4/2/2018 |
c5 | schmidt | Site2 | 4/1/2018 |
c6 | nereese | Site1 | 4/1/2018 |
c7 | juanita | Site3 | 4/2/2018 |
c8 | oscar | Site2 | 4/2/2018 |
c9 | ivan | Site1 | 4/1/2018 |
c10 | joe | Site1 | 4/1/2018 |
c11 | ahmed | Site1 | 4/2/2018 |
c12 | joe | Site2 | 4/4/2018 |
c13 | juanita | Site1 | 4/1/2018 |
c14 | ahmed | Site3 | 4/2/2018 |
c15 | oscar | Site2 | 4/1/2018 |
c16 | sally | Site1 | 4/2/2018 |
c17 | schmidt | Site2 | 4/1/2018 |
c18 | juanita | Site1 | 4/2/2018 |
c19 | oscar | Site2 | 4/2/2018 |
c20 | juanita | Site1 | 4/1/2018 |
Emploees:
Employee | inits |
juanita | 1 |
joe | 2 |
ahmed | 3 |
schmidt | 4 |
oscar | 5 |
bob | 6 |
sally | 7 |
nereese | 8 |
ivan | 9 |
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...
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.
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
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)
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
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
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
SiteID | SiteName | inits | O Mkt | D Mkt |
Site1 | Happy Apple Makers | 1 | a | c |
Site2 | The Most Interesting Man in the World | 2 | b | a |
Site3 | Sheep Sheerers | 3 | d | b |
Site4 | Grub's Grub Restaurant | 4 | b | c |
Site5 | Golum's Precious Finders | 5 | c | d |
Calls table
CallID | Caller | SiteID | Date |
c1 | bob | Site1 | 4/1/2018 |
c2 | joe | Site3 | 4/1/2018 |
c3 | sally | Site2 | 4/1/2018 |
c4 | ahmed | Site2 | 4/2/2018 |
c5 | schmidt | Site2 | 4/1/2018 |
c6 | nereese | Site1 | 4/1/2018 |
c7 | juanita | Site3 | 4/2/2018 |
c8 | oscar | Site2 | 4/2/2018 |
c9 | ivan | Site1 | 4/1/2018 |
c10 | joe | Site1 | 4/1/2018 |
c11 | ahmed | Site1 | 4/2/2018 |
c12 | joe | Site2 | 4/4/2018 |
c13 | juanita | Site1 | 4/1/2018 |
c14 | ahmed | Site3 | 4/2/2018 |
c15 | oscar | Site2 | 4/1/2018 |
c16 | sally | Site1 | 4/2/2018 |
c17 | schmidt | Site2 | 4/1/2018 |
c18 | juanita | Site1 | 4/2/2018 |
c19 | oscar | Site2 | 4/2/2018 |
c20 | juanita | Site1 | 4/1/2018 |
Employees
Employee | inits |
juanita | 1 |
joe | 2 |
ahmed | 3 |
schmidt | 4 |
oscar | 5 |
bob | 6 |
sally | 7 |
nereese | 8 |
ivan | 9 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |