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

[sohananahid]

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

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

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!

[sohananahid · Accepted solution]

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! 🙂

[v-cgao-msft]

hi @sohananahid ,

thanks for sharing!🤗

Best Regards,
Gao
Community Support Team

[v-cgao-msft]

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