Two Tables, Nothing but Zeros

[pjpreddy2]

Hi All,

I've been staring at this issue and need a second set of eyes.  I've got two tables, TABLE1 a parent table with unique values and TABLE2 with values I need to slice and work with.  What I'm trying to do is have a list of locations (sorted by parent location) that have no values in Table 2.  I've tried a few things but can't get it to work. 

Table 1
Parent#	Parent Name	Loc#	Location Name
29	Pittsburgh	9929	Region LDR
29	Pittsburgh	2980	Murrysville
29	Pittsburgh	2979	Wexford Flats
28	Ohio	2890	Cedar Cliff
28	Ohio	2889	Carlisle
28	Ohio	2888	Simpson Ferry
28	Ohio	2887	Hampden Center
28	Ohio	2886	Lemoyne
28	Ohio	2885	Coventry Mall
32	Virgina	2830	Westlake
32	Virgina	2829	Hudson
31	Maryland	2761	Catonsville
31	Maryland	2760	Carney
31	Maryland	2759	Lombard Street
31	Maryland	2758	Waugh Chapel
31	Maryland	2757	Annapolis Towne Centre
31	Maryland	2755	Baywoods

Table 2
Parent#	Parent Name	Loc#	Location Name	Value
29	Pittsburgh	9929	Region LDR	123698
29	Pittsburgh	2980	Murrysville	5555
28	Ohio	2888	Simpson Ferry	2000
28	Ohio	2887	Hampden Center	400
32	Virgina	2830	Westlake	398
31	Maryland	2759	Lombard Street	33558
32	Virgina	2830	Westlake	350
31	Maryland	2759	Lombard Street	4000
29	Pittsburgh	9929	Region LDR	200
29	Pittsburgh	2980	Murrysville	1000
28	Ohio	2888	Simpson Ferry	40
28	Ohio	2888	Simpson Ferry	100

I've tried

Zero's = IF(Table 2[Value]=0,"zero","Not Zero")

As a calculation but it's not returning the correct answer. 

Thanks Everyone!

[Anonymous]

We can also achieve this by leveraging RELATEDTABLE. Create a calculated column using

No Record's = COUNTROWS(RELATEDTABLE(Table 2[Value])) and then filter "No Record's" = Blank or 0 to display the records with no values in Table 2.

Please let me know if this worked.

@Greg_Deckler: Huge fan of yours and I started following the Power BI community because of your answers. You are a great asset to the PBI community. Thanks for all your tips and answers. 

[Greg_Deckler]

I believe what you want for your calculation (in a calculated column in Table1) would be:

Zero's = IF(ISBLANK(CALCULATE(SUM(Table 2[Value]))),"zero","Not Zero")

Assuming you have a relationship on "#Loc" columns.

[alexei7]

Hi,

Presuming that you've joined both table by the "Loc#" field, you could just bring in "Loc#" from the parent table and "location name" from the location table into a Power BI table visual and then filter the visual to only show where "location name" is blank.

[pjpreddy2]

No that is not working (that was the first thing I did), I'm using the locations from the Table 1 and trying to tie the values into Table 2, but you're seeing a simplified version of the data.  There's a lot more complexity here that is likely causing the problem, but I've sort of stared at this for so long I can't see the answer.