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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dtbingh
Helper I
Helper I

How can I get the count of records from a filtered matrix table?

I have a matrix with 3 rows.. Showing the Company name, the content role function(aka department), and email address. I simply want to have another column that will count the total number of accounts(email addresses) per department. My issues is that every time I do the count just shows the total email addresses, and isn't filtered by the specific companies department I'm looking at. 

 

This is the table currently: 

dtbingh_0-1626383580681.png

I can't click the drop down because there are email addresses, but for example if you look at Armstron Backus & Co. under Audit and Accounting there are 3 email addresses, meaning there are 3 accounts. How do I get the column on the right to show the number 3 next to Audit and Accounting?? Also, have the total number of accounts next to the company name. 

 

1 ACCEPTED SOLUTION

After looking through other forums I tried using the Arcterys Matrix Light V1.0.2 and now the total next to company name is being summed correctly. I'm not sure if this is an issue with Power BI Matrices like it seemed to be in the post I found from 2017, or if the issue is with my underlying model diagram.. 

Anyways, to sum it up incase someone happens by this post. 

1. You'll want to visit: https://docs.microsoft.com/en-us/power-bi/guidance/relationships-troubleshoot to fix your relationships

2. If the totals are still wrong then try using the aforementioned Matrix available right from the marketplace!

View solution in original post

6 REPLIES 6
HotChilli
Super User
Super User

This is a classic sign of a missing or incorrect relationship.  Would need to see a model diagram (but would prefer a pbix with test data)

Thought I should have included that in the original post. 

 

Here it is:

dtbingh_0-1626384895054.png

 

I actually think I just inched a little closer by changing the cross filter direction to both. 

dtbingh_0-1626385467232.png

Now just the total next to company name is wrong

I can't tell what fields are included in the visual and have you changed the count field since the first post?

 

The model doesn't look ideal for reporting purposes.  It looks like ContentRoleLookup... is a dimension table but it's being treated as a fact table.  Contacts is the reverse of that. 

Ultimately it depends what other things are going to be shown in visuals.

If you explain the fields in the visual, I'll see if I can help with that.

So I didn't change the count at all, rather I just changed the cross filter direction in the model diagram and that enabled the count for department to total correctly. My issue now is that the count across from company name is adding up incorrectly and is not totalling all the accounts from each department.

 

Here is the complete report:

dtbingh_0-1626444597239.png

 

I'm not really sure how to go about fixing the underlying table structure, I tried to mimick what I saw in Dynamics CRM

 

After looking through other forums I tried using the Arcterys Matrix Light V1.0.2 and now the total next to company name is being summed correctly. I'm not sure if this is an issue with Power BI Matrices like it seemed to be in the post I found from 2017, or if the issue is with my underlying model diagram.. 

Anyways, to sum it up incase someone happens by this post. 

1. You'll want to visit: https://docs.microsoft.com/en-us/power-bi/guidance/relationships-troubleshoot to fix your relationships

2. If the totals are still wrong then try using the aforementioned Matrix available right from the marketplace!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.