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
jmunatec
Regular Visitor

Count of record in child table causing records to be dropped

Hello all, New to Power BI but fairly familiar with SQL concepts. I have two main tables, "message" and "favorited_by". The "message" table is composed of the following columns: -message_id -user_id -text -create_date The "favorited_by" table is composed of the following columns: -message_id -user_id The "favorited_by" table represents the users who favorited a message. If a message was not favorited, it is not represented in the "favorited_by" table. If a message by favorited by two people, it would have two records in the "favorited_by" table with the message_id and the two user_ids of the people that favorited it. These two tables are currently modeled with the "message" table on the one and the "favorited_by" table on the many; cross filter direction of single. My goal is to create a table that shows the message columns along with a count of users that favorited the message. I have created a measure, Count of Favorites = COUNT(favorited_by[user_id]). This measure returns the correct count of favorites for each message; but when I combine this measure with my "message" table, I am filtering out records that do not have any favorites at all; instead I would like to keep those records from the "message" table and display a 0 for the Count of Favorites. I assume this behavior is due to my "favorited_by" table only containing message_ids that have associated favorites. Is there something I can do to create more of a LEFT JOIN behavior between the parent and child table? Thank you for any guidance!
1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

Hi @jmunatec

 

You may use below measure:

Count of Favorites =
COUNT ( favorited_by[user_id] ) + 0

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-cherch-msft
Employee
Employee

Hi @jmunatec

 

You may use below measure:

Count of Favorites =
COUNT ( favorited_by[user_id] ) + 0

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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