Forum Discussion

pjpreddy2's avatar
pjpreddy2
Frequent Visitor
8 years ago

Two Tables, Nothing but Zeros

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 NameLoc#Location Name
29Pittsburgh9929Region LDR
29Pittsburgh2980Murrysville
29Pittsburgh2979Wexford Flats
28Ohio2890Cedar Cliff
28Ohio2889Carlisle
28Ohio2888Simpson Ferry
28Ohio2887Hampden Center
28Ohio2886Lemoyne
28Ohio2885Coventry Mall
32Virgina2830Westlake
32Virgina2829Hudson
31Maryland2761Catonsville
31Maryland2760Carney
31Maryland2759Lombard Street
31Maryland2758Waugh Chapel
31Maryland2757Annapolis Towne Centre
31Maryland2755Baywoods

 

Table 2
Parent#Parent NameLoc#Location NameValue
29Pittsburgh9929Region LDR123698
29Pittsburgh2980Murrysville5555
28Ohio2888Simpson Ferry2000
28Ohio2887Hampden Center400
32Virgina2830Westlake398
31Maryland2759Lombard Street33558
32Virgina2830Westlake350
31Maryland2759Lombard Street4000
29Pittsburgh9929Region LDR200
29Pittsburgh2980Murrysville1000
28Ohio2888Simpson Ferry40
28Ohio2888Simpson Ferry100

 

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!

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
    alexei7
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

    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's avatar
      pjpreddy2
      Frequent Visitor
      8 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.