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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sohananahid
Post Partisan
Post Partisan

Count of records not showing right in a Chart Visual with joining 2 tables

Hi all,

I am working on a Power BI Report that has a dataset/ table - "Employees". To add another page with new functionality- I am connecting to another dataset/table "Action Items" via Direct Query. I did "Manage Relationship" to join these 2 tables with "Employees[Employee Name]"  and "Action Items [Responsible Person]" On the new page, I have a

- "Team" slicer [this field is from the "Employees" table] to filter data specific to a "Team"

-"Detailed Table" visual to show Columns from both tables-  "Employee Name" [this one is coming from the "Employees" table] and remaining fields- "Action Id", "Action Due Status", "Scheduled Date", "Description" … coming from  "Action Items" table

-A Line and Stacked Column chart - X-axis: "Scheduled Date", Column y-axis: Action_Item Count [ a measure which basically does COUNTROWS('Employees')], Column Legend: "Action Due Status"

 

So far all is good! For example, for Team: "Machinery", there are 3 rows filtered and they are shown both in the "Detailed Table" down and in the Chart on Top Right. [Here "Responsible Person" is the same Person, "Sarah I.", it may be "Sarah I.", "John Doe", … in some instances.]

 

sohananahid_0-1661550752345.png

-Issue:

Now I need another similar "Line and Stacked Column chart" chart this time X-axis: "Action Items [Responsible Person]" or "Employees[Employee Name]" instead of "Scheduled Date". This time it shows only 1 "Overdue" [instead of 2 "Overdue" as shown in the "Detailed Table"]. It shows 1 "Due > 30 days" which is good

-So, looks like it's good when there is only 1 record/row for an "Action Due Status", but when there is more than 1 record for the same "Action Due Status", e.g., here 2 records for "Overdue" status- it shows only 1.

 

sohananahid_1-1661550752374.png

 

 

I also tried to change to do the count in different ways, e.g. with the measure now doing COUNT('Action Items '[Action ID])- nothing helped!

Note: I simplified the Column Names in the post from the actual names for convenience!

Could anyone help me to solve this issue, would appreciate it a lot?

Thanks!

1 ACCEPTED SOLUTION
sohananahid
Post Partisan
Post Partisan

Hi @v-cgao-msft , Thanks for reaching out! Actually I just could resolve it myself! To others who may have similar issues: While doing "Manage Relation" for the join of 2 tables- don't rely 100% on Power BI to detect the "Cardinality" and "Cross Filter Direction" for the join! Please check the "Relation" model and make sure the "Cardinality" and "Cross Filter Direction" for the joins between 2 tables [where the data is being pulled from in the visuals] are correct. That's what I found the issue was and as I corrected those 2 items- things are showing up good now. Ciao! 🙂

 

View solution in original post

3 REPLIES 3
sohananahid
Post Partisan
Post Partisan

Hi @v-cgao-msft , Thanks for reaching out! Actually I just could resolve it myself! To others who may have similar issues: While doing "Manage Relation" for the join of 2 tables- don't rely 100% on Power BI to detect the "Cardinality" and "Cross Filter Direction" for the join! Please check the "Relation" model and make sure the "Cardinality" and "Cross Filter Direction" for the joins between 2 tables [where the data is being pulled from in the visuals] are correct. That's what I found the issue was and as I corrected those 2 items- things are showing up good now. Ciao! 🙂

 

hi @sohananahid ,

 

thanks for sharing!🤗

 

Best Regards,
Gao

Community Support Team

v-cgao-msft
Community Support
Community Support

Hi @sohananahid ,

 

Are the totals correct before putting in Legend? Can you provide sample data for testing, thanks in advance!

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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