Forum Discussion
Best Solution for Month to Month Analysis without Causing Duplicate Data Errors.
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 Query
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?