Forum Discussion

mjmm's avatar
mjmm
Frequent Visitor
2 years ago
Solved

Adding missing rows to my dataset

Dear all, 

I am new to PowerBI and I have a question I hope someone can help me with. Thanks inadvance for any help I will receive, it's much appreciated. 

I have the following (example) dataset

Name

Team

Projectcode

Hours

Ma

A

10

8

Ma

A

20

4

Fl

A

10

16

An

B

20

24

Ja

B

10

12

Ja

B

20

8

Do

A

20

6

Li

A

10

8

Fr

C

10

2

Fr

C

20

24

Au

C

20

18

Ve

C

10

12

Ri

C

20

8

Ri

C

10

6

 

I created a stacked bar chart showing the sum of Hours for each Team. 
I then use a slicer to filter out only rows where the Projectcode = 10
In this example, Team A has a sum of 32 hours spent on Projectcode 10.

 

 

 

When I create a data table showing more details on how much hours each name spent on this project, I see this: 


Obviously, this makes sense because the other name in team A, "Do" hasn't spent hours on projectcode 10, only on projectcode 20. Unfortunately I do not have there rows in the dataset.

I would like to find a way to also show the team members that have not spent hours on this project in the table. In this case I would like "Do" to show up in the table with a value of '0' or with a ‘Blank’.

Is there a way for me to do this? Of course the ‘Show items with no data’ option does not work since there’s no row in the dataset.

Much appreciated.

Mark

 

 

Need Help
  • Gabry's avatar
    Gabry
    2 years ago

    Hello Mark, 
    You need to modify your model I suppose. Do you have just one table?

    You need to create a new table with the team members, like this:

     

    TeamMembers = SUMMARIZE(
       'Table',
        'Table'[Name],
        'Table'[Team]
    )

    Then you need to connect the teammembers table to the original table by names
    Then create the formula: Total Hours = SUM('Table'[Hours])

    Then use the TeamMembers table as the basis for your table visualization. Include the Name, Team, and your Total Hours measure.

    Then in the table visualization settings, ensure that "Show items with no data" is enabled for the Name field.



    If this helps pls accept the solution and kudos 😉

    PS. 

    Since the relationship with the `Name` field is obviously weak, you might need to consider adding IDs

  • AnkitaaMishra's avatar
    AnkitaaMishra
    2 years ago

    Hi mjmm , Hope you are doing well.

    Please follow below steps for your requirement : 
    Step 1 : Create a summarize table with Name, Team and Concat columns as shown below :

    Step 2 : Create a concatenated column using Name and Team column in your original table to build relationship between summarized table and original table : 

    Step 3 : Build a measure with below DAX for calculating hours : 

    TotalHours =
    Var tHours = CALCULATE(
        SUM('Table'[Hours]),
        ALLSELECTED('Table'[Projectcode])
    )
    RETURN
    IF(
        ISBLANK(tHours),
        0,
        tHours
    )
    Below is the output of above steps :


    Let me know if this works for you else share more details about your requirement

     

    Thanks,

    Ankita

3 Replies

  • Gabry's avatar
    Gabry
    Super User
    2 years ago

    Hello Mark, 
    You need to modify your model I suppose. Do you have just one table?

    You need to create a new table with the team members, like this:

     

    TeamMembers = SUMMARIZE(
       'Table',
        'Table'[Name],
        'Table'[Team]
    )

    Then you need to connect the teammembers table to the original table by names
    Then create the formula: Total Hours = SUM('Table'[Hours])

    Then use the TeamMembers table as the basis for your table visualization. Include the Name, Team, and your Total Hours measure.

    Then in the table visualization settings, ensure that "Show items with no data" is enabled for the Name field.



    If this helps pls accept the solution and kudos 😉

    PS. 

    Since the relationship with the `Name` field is obviously weak, you might need to consider adding IDs

  • AnkitaaMishra's avatar
    AnkitaaMishra
    Super User
    2 years ago

    Hi mjmm , Hope you are doing well.

    Please follow below steps for your requirement : 
    Step 1 : Create a summarize table with Name, Team and Concat columns as shown below :

    Step 2 : Create a concatenated column using Name and Team column in your original table to build relationship between summarized table and original table : 

    Step 3 : Build a measure with below DAX for calculating hours : 

    TotalHours =
    Var tHours = CALCULATE(
        SUM('Table'[Hours]),
        ALLSELECTED('Table'[Projectcode])
    )
    RETURN
    IF(
        ISBLANK(tHours),
        0,
        tHours
    )
    Below is the output of above steps :


    Let me know if this works for you else share more details about your requirement

     

    Thanks,

    Ankita

  • mjmm's avatar
    mjmm
    Frequent Visitor
    2 years ago

    Thanks to both, I ended up using a combination of both your solutions. 
    Much appreciated for the fast help!

    As for the model, I am aware this needs to change. Unfortunately, for the short term, I am stuck with a flat Excel file. So as a temporary solution this is perfectly fine. Again, thanks!