Add records to missing items

[Anonymous]

Hi everyone!

I would like to ask for your precious help for an issue that I cannot solve. I am not a very experienced PowerBI user.

I received data from a database (BIMcollab) concerning the progress of a project.

The primary key, i.e. the unique code automatically generated by the database when a new issue is created, is named "Index".

As you can see in the picture below, several IssueAction (Created, Edited, Resolved, Closed) are executed for each "Index" item, both within the same week and in different weeks.

Our goal is to create a graph like the one in the picture below, showing the total number of IssueAction executed in each week of the calendar.

However, In PowerBI we get a discontinuous chart, because some actions in some weeks have not been executed (e.g. Pivot below). In addition, in weeks where there was no activity, the row is completely missing.

I have tried to figure out how to insert a value =0 for the missing IssueAction, but I don't understand how this is possible because the IssueAction should have a link to the primary key "Index".

I hope I was able to explain what I meant. Do you have any ideas?

Thanks a lot!

Alberto

[Anonymous]

Hi @BA_Pete @Icey  @jpresley !
Thanks a lot for your answers and advices.

After your suggestions the chart lines are finally connected!

However, weeks for which there is no data still do not appear on the X-axis (e.g. 2020-30 to 2020-37).

I share an example file here , in case you would like to have a look at it.

Thanks,

Alberto

[BA_Pete · Accepted solution]

Hi @Anonymous ,

I'll refer you back to my original answer: add "+0" to the end of your 'IssuesActionCount v2" measure. This will display all possible axis values, but you will need to limit the viewable time period of your chart with either a page slicer or visual filter as this does, indeed, show ALL possible axis values.

As an aside: I can see from your model that you have a bi-directional many-to-many relationship between your DimKalender and Activity tables. This is strongly recommended against, unless you are VERY clear on many-to-many relationship behaviour. Generally accepted practice is that anything that is intended/modelled as a dimension table sits on the One side of a One-to-Many relationship (or One-to-One, obviously).

To remedy this in your model, I would recommend creating a new column in your Activity table that is just the date portion of Activity[TimeStamp], and relate DimKalender[Date] to Activty[Date].

Pete

[Icey]

Hi @Anonymous ,

Please create another Date dimension table which with all weeks. And then create relationship between your data table and this Date dimension table. Finally, use the "Week" column ffrom this Date dimension table instead of the "JahrKW" column from your data table and you will get what you want.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[BA_Pete]

Hi @Anonymous ,

I may be oversimplifying your requirements, but have you tried just adding a '+ 0' to the end of your measures?

It would look something like this:

_noofActivities =
CALCULATE(
  COUNTROWS(yourTable),
  yourTable[IssueAction] = "Closed"
) + 0

Pete 

[Anonymous]

Hi @BA_Pete ,

thanks for your reply.

I tried to follow your advice, unfortunately without success.

The IssueAction column is not a measure, but an imported field from the Database. I tried to create a new measure using the code you wrote but the graph, when I select more than one IssueAction in the filter, is still discontinuous.

[jpresley]

I don't know what your measure formula is but let's assume it's a simple count so

IssueActionCount = COUNT ( 'table'[IssueAction] )

 What might work for you is use IF to find if a day has any actions, and if so, then see if a specific action type has any rows. If not, return a 0 for a specific action. But if there are no actions at all for a given day, then return a blank.

[IssuesActionCount v2] =
VAR IssuesAllCount =
CALCULATE (
COUNT ( 'table'[IssueAction] ),
    ALL ( 'table'[IssueAction] )
)
VAR IssueCount =
COUNT ( 'table'[IssueAction] )
RETURN
IF (
    ISBLANK ( IssuesAllCount ),
    BLANK (),
        IF ( ISBLANK ( IssueCount ), 0, IssueCount )
)

This measure should return a blank if there are no actions at all for a given day, like say a weekend, but if that isn't desired you can remove the first condition and the VAR "IssuesAllCount". This also assumes you have a table for dates in relationship to your fact table otherwise it won't work.

This report uses the measure [IssuesActionCount v2]. In the fact table there are no records on 1/2/2020 for the "Edited" action but the report shows a 0.

[BA_Pete]

Hi @Anonymous ,

If you're able to share an example of your data that I can use, or your PBIX, I'm happy to take a proper look for you.

Please remove any sensitive data before sharing.

Pete