Forum Discussion

josborne's avatar
josborne
Helper I
10 months ago
Solved

PowerBI - table join question

Hi all.  Hoping to get some advice, I just can't seem to work this out.

I've got 2 tables:

Table 1 - name ending in 'main'

- primary data including project name and other details

- contains 'projectid' field to join to 'effort' table (red box in the image)

Tabvle 2 - name ending in 'effort'

- contains effort data which is to compliment data in the 'main table  (green box in the image)

- contains 'projectid' field to join to 'main' table (red box in the image)

I've created a relationship between 'projectid' fields. When I create a table from the 'main' table I see all projects.  When I add in effort from the 'effort' table, I only see projects which have effort in the 'effort' table, projects with no effort dissapear. if there is no effort for that project, the project will not appear in the 'effort' table.

So my question is, how to I still see all projects from the 'main' table, and if there's no effort (which means no 'projectid' in the effort table), I get a 0 value instead of the project not appearing?

I have a feeling this is straightforward but I haven't been able to crack it!

 

 

Thanks in advance.

 

James

 

Need Help
  • rohit1991's avatar
    rohit1991
    10 months ago

    Hi josborne 

    Could you please try below measure: 

    Total Effort = 
COALESCE (
    SUM ( Effort[EffortDays] ),
    0
)

    • SUM gives blank if no effort rows exist.

    • COALESCE converts that blank into 0.

      Outcome:

       

     

5 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    10 months ago

    Hi josborne 

    Could you please try below measure: 

    Total Effort = 
COALESCE (
    SUM ( Effort[EffortDays] ),
    0
)

    • SUM gives blank if no effort rows exist.

    • COALESCE converts that blank into 0.

      Outcome:

       

     

  • mh2587's avatar
    mh2587
    Super User
    10 months ago

    Create following measure and put it into a table, you will have 0 instead of blank days which disappear or simple enble "Show items with no data" in table visual


    Total Effort Days =
    SUM('sw_pat_tbl_effort'[sw_effordays]) + 0

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    10 months ago
    Hi josborne  ,

    I would also take a moment to thank mh2587 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

  • josborne's avatar
    josborne
    Helper I
    10 months ago

    Thanks all I really appreciate your help.  Let me clarify one thing if I may.  I'm adding the effort values from table 2 ('main') to table 1 ('effort). Some projects ('projectID' field) don't exist in table 2 because there is no effort against those projects. When I add the tables together into one visual (table or matrix) the projects which have no effort in the effort table are removed from the visual.  In other words, if I have 10 total projects and 5 have effort, my visual has all 10 projects until I add in the effort field then the visual only shows the 5 projects with effort.