cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lenihan
Helper III
Helper III

Best Solution for Month to Month Analysis without Causing Duplicate Data Errors.

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 NameMonthDescription
CI00000007917Config One

 Jan

Blue
CI00000008326Config twoJan Red
CI00000007557Config threeJan Green
CI00000008017Config fourJan Green2
CI00000007137Config five JanPurple

 

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.

1 ACCEPTED 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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

12 REPLIES 12
GilbertQ
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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?

MalS
Resolver III
Resolver III

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:

 

CI Tables.png

 

 

 

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thank you for the help @GilbertQ and @MalS.  It appears using the calculated table has made things easier and I believe it is all working! 

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.

Lenihan
Helper III
Helper III

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)

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.