Return Count of Rows Where Key Not Referenced

[dday9]

I am trying to return the count of rows from TableA that are not referenced in TableB, so for my raw SQL I setup the following query:

SELECT TableA.Id, TableB.TableAId
FROM TableA
LEFT OUTER JOIN TableB
	ON TableA.Id = TableB.TableAId
WHERE TableB.TableAId IS NULL;

When running this command it returns 64,726 rows.

However, whenever I:

1. Drag TableA.Id in Power BI to display it in a grid
2. Change the Id in Values from Don't Summarize to Count
3. Drag TableB.TableAId into the Filters for the respective grid
4. Change the filter type to Advanced Filtering
5. Set the Show items when the value dropdown to Is Blank

The count displayed in the grid is 64,732.

I clicked on Refresh to ensure that my local datasource in Power BI was up-to-date with my database, but the results did not change. This leads me to believe that what I'm doing in Power BI is not accurately reflecting what I would do using the raw SQL. Is there anything noticeable that I'm doing wrong here?

Edit - It is worth noting that I just ran the following query and it returned 6 rows, the exact difference between my raw SQL and what I'm doing in Power BI:

SELECT DISTINCT TableB.TableAId
FROM TableB

Edit #2 - After digging around I came across this thread: https://community.powerbi.com/t5/Desktop/List-rows-that-are-missing-in-another-table/td-p/272307 the issue I run into implementing this is applying a date range based on a DateTime column on TableB because whenever I attempt to setup the relationship from TableA to the table created in Power BI this causes a circular dependency issue.

Edit #3 - I came across this solution https://community.powerbi.com/t5/Desktop/How-to-count-rows-that-are-related-to-another-table/td-p/19... which works for me, I just wish this could be done without the need to create a new measure.

[v-xicai]

Hi @dday9 ,

If you wish this could be done without the need to create a new measure or new table, recommend you post your issue in SQL Server Community forum for further professional help .

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dday9]

Thank you Amy, but just to clarify I do have a working SQL solution and ultimately I wound up going with running a CTE when importing my data rather than using a Measure. My issue revolved around achieving the same thing, only using a pure Power BI solution.