Forum Discussion

Lenihan's avatar
Lenihan
Icon for Helper III rankHelper III
9 years ago
Solved

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 bec...
Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    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

Lenihan's avatar
Lenihan
Icon for Helper III rankHelper III
9 years ago

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.

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
9 years ago

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?

  • Lenihan's avatar
    Lenihan
    Icon for Helper III rankHelper III
    9 years ago

    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?

    • GilbertQ's avatar
      GilbertQ
      Icon for Super User rankSuper User
      9 years ago

      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

      • Lenihan's avatar
        Lenihan
        Icon for Helper III rankHelper III
        9 years ago

        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.