Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I'm trying to do a month to month analysis of data that does not have unique values on the key column. Now that I have to add another month on, I've come across a snag. I can't add the data because it creates a condition where the unique column is no longer unique.
This is Configuration Items data.
Here is some fictitious data that would hopefully explain what I'm trying to do:
CI Identifier (unique) | Display Name | Month | Description |
CI00000007917 | Config One | Jan | Blue |
CI00000008326 | Config two | Jan | Red |
CI00000007557 | Config three | Jan | Green |
CI00000008017 | Config four | Jan | Green2 |
CI00000007137 | Config five | Jan | Purple |
Essentially, the CI Identifier field is unique. Each month, i have to spit out statistics on those Unique CIs (about 90K records). I need to graph the results that displays month over month. I add a column called Reporting Month each month so that I can tell the difference between the records. I thought this was my solution easily enough.
So - in January, CI00000008017, Config Four, Green might have a status of Active.
In February, that same CI00000008017, Config Four, Green might have a status of PreProduction, or maybe Removed for example. When I tried to append the February data, which includes the same Unique CI identifiers, they are now no longer unique! There is one for each month - so it errored out because this is the column I use for the relationships to the other tables. It is the only unique column I have. I don't think I can use an index, because that wouldn't be the column to use for the relationship tables.
I hope this makes sense, as I can't share my data outside my organization.
So, the question is - how do other people do a month over month analysis of data that have unique values in a column, that is used for the other table relationships. The data isn't data driven. When I do change record reports, its easy because I'm only pulling change records specific to the month. A change record that happens in January doesn't exist in February as well.
Solved! Go to Solution.
Hi @Lenihan,
What I was suggesting is that with the calculated table you can get all the unique CI Identifier across all your tables.
Then from tables you would create a relationship to the Calculated table.
What that then means is you could then use the Calcualted Table as a filter. And it will filter all the other tables?
Here is a blog post explaining how to create the Calculated Table - Connect any number of tables together via a common column
Hi @Lenihan,
Another suggestion that I would make is to create a Date table. By having a Date table it makes it very easy to use the built in time-ingelligence features in Power BI (SAMEPERIODLASTYEAR, PARALLELPERIOD, etc) to compare Month to Month or Cumulative Rolling periods also.
If you need to understand what a Date table does you can read this blog post: Do You Need a Date Dimension?
Next if you now want to go ahead and create a date table, here is my blog post explaining how to easily do this: Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Quer...
Then what I would in the Query Editor is because you know when your data is coming in, I would create another column in which you can use the Month and Year to create a Date column. So that the column would look like 01/01/2017 which is effectively made up of "01/" & [Month] & "/"& [Year]
Once the above is done in your Query Editor you can then load the data and create the relationship from your table to your date table.
I do have a date table for use for some of the graphs, however, many of my comparisons I apply are not date driven. For example, the config item status in January could be Active, but then in February it is Removed - so it is the same config item but different field values that are not date dependent. I need the table relationship to be on the CI identifier column though because that is used for cross referencing the tables.
For example, the Config Item in the CI identifier column in table 1 is selected, and it does a lookup on the relationship table to see what downstream relationships exist. These are two separate tables because in ServiceNow this information is not available in one table.
Can Power BI take data from column A in table 1, find it in column A in table 2 and relate all the other information, if the relationship link is based on the Date column rather than the Column A's?
Can you use the CI data as a lookup table, and create a new data table that contains the Month and Status info, then join them?
For example:
That is how I have it right now, but when I go to add the new month of data, since the CI Identifier is the column I'm linking the relationship on, it won't allow duplicate values. Each month I add will duplicate every record. In my example, status was just one column, I have about 20ish that I use for various reports.
Hi @Lenihan,
What you could do is to create a calculated table which has the unique CI Identifiers from both tables.
Then create a relationship from each table to your calculated table?
Hi @GilbertQ
So similar to what I was suggesting then with the CI identifier merged with the reporting month? If I create that calculated column on each table then use that as the relationship. I guess this would mean any measures i create I would need to use the dax function userelationship for any formulas that require data from both tables?
Hi @Lenihan,
What I was suggesting is that with the calculated table you can get all the unique CI Identifier across all your tables.
Then from tables you would create a relationship to the Calculated table.
What that then means is you could then use the Calcualted Table as a filter. And it will filter all the other tables?
Here is a blog post explaining how to create the Calculated Table - Connect any number of tables together via a common column
Hi,
If I was also to add a master calendar table, how would that connect with the calculated table? Would it be separate from the dax expression so that it is not factored in for the calculated column ? Or, not even related to the calculated table, and I could still relate the date table to the other tables?
Hi @Lenihan
When you create a calculated table, once it has been created it looks exactly the same as a table where you have imported your data. If you look closely you will see that the calculated table has got a slight blue colour to it.
From there you can then create any measures or create relationships back to your Master Calendar table.
Hi guavaq,
That sounds possible to help. I'm going to start over from scratch because I've just tried so many different things, that I can no longer document what I've been doing! I'm going to start by doing this and see what happens. Thank you for the info.
Thought of a possibly solution.. but hoping there is something better.
I could concatenate the CI identifier field with the Month Field. This would give me a unique value each month for the CIs - but, this would mean I'd have to do that for every table (create a concatenate field) else I wouldn't be able to use it for the relationships. (Hopefully this helps explain what I'm trying to do)
User | Count |
---|---|
128 | |
73 | |
70 | |
58 | |
53 |
User | Count |
---|---|
193 | |
96 | |
66 | |
62 | |
52 |