Forum Discussion
Add records to missing items
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
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
7 Replies
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" ) + 0Pete
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.
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
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.
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
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
