Table is not showing rows with count of 0 they are getting filtered out altogether

[RubySmith]

Hi, 

I'm very new to PowerBi and am experiencing the following issue

I have 2 tables. 

The first has a full list of Users and Licenses assigned. The second has a list of Purchase orders and Users that requested them. 

I have linked the tables by Users and Users (many to many). 

I have created a table visual where I want to show the full list of Users (filtered for Finance License) and the number of Purchase Orders (if any). 

The table is being filtered when I add Count of Purchase Orders, to show only the users that are in both tables (so removing any users that do not have Purchase Orders). In my example User 4 has no Purchase Orders and once I add the Count of PO to the visual, User 4 is dropped from the list. 

Seeking assistance to show all Users, and just the count of Purchase Orders either 0 or 1+. 

Thanks in advance!

Table 1

User	License
User 1	Finance
User 1	Sales
User 2	Field Service
User 2	Finance
User 2	Customer Service
User 3	Finance
User 3	Sales
User 4	Finance
User 5	Finance

Table 2

Purchase Order	User
PO1	User 1
PO2	User 1
PO3	User 2
PO4	User 2
PO5	User 2
PO6	User 3
PO7	User 3
PO8	User 5
PO9	User 5

[v-nmadadi-msft]

Hi @RubySmith ,

We have not heard back from you if any of the answers has met your requirement kindly  Accept it as the solution  to help out other community members or get back to us with any other questions.
Thank you

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Bibiano_Geraldo]

Hi @RubySmith ,

As mentioned by @v-nmadadi-msft , be aware always you can from many-to-many relationships.

For your scenario, you need to create a third table that will be related for table1 and table2 by this DAX:

Users = DISTINCT(UNION(VALUES(Table1[User]), VALUES(Table2[User])))

Above DAX will return This table:

Relate this table to both Table 1 and Table 2:

Now create a measure with the following DAX:

Count of Purchase Orders = 
CALCULATE(
    COALESCE(
        COUNT(Table2[Purchase Order]),
        0
    ),
    FILTER(
        Table1,
        Table1[License] = "Finance"
    )
)

Now create a Matrix visual, and drop the user column from the created users table in rows field, and on the values field add the created Count of Purchase Orders measure, and your Matrix will look like this:

[v-nmadadi-msft · Accepted solution]

Hi @RubySmith  ,

As mentioned by @collinq     many to many relationships are generally avoided as much as possible and I have created a new table consisting of users and modified the relationship like this.

Coming to your main issue, Power BI is filtering out user4 because they don’t have any purchase order, if you do want to see the visual count of 0 for user4 we have to use a Dax command

“CountQuantity =

COALESCE(COUNT(Sheet2[Purchase Order]), 0)

“

With this method we will be able to view the count of user4. We can remove the count of purchase order from our columns but I have included it just to show the difference.

Read more about coalesce here: COALESCE function (DAX) - DAX | Microsoft Learn

Please consider Accepting the solution if the provided answer meets your requirement so as to help other users or get back to us if you have further queries.

Thanks and Regards

[RubySmith]

Thanks that worked! appreciate your assistance 

[collinq]

Hi @RubySmith ,

While it is possible and sometimes necessary to have a many-to-many relationship this is not common and can cause it's own unique issues - including odd results like blanks (or zero) being removed.  Since you mentioned that you are new,  Iwould suggest some light reading (lol) in this article: Many-to-many relationship guidance - Power BI | Microsoft Learn

Particularly review the grain level issues where data can be "removed" or ignored or lost.

If there is any way to get around the many-to-many (like a bridge table) then I prefer that method.  Again, I understand in some cases there is no way around it but you have to be very careful about many-to-many relationships.