cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.