Multi Table Pivot - Turning Excel into a Relational DB

Hi Anonymous ,

Quick answer: You can only compare measure values from each fact table along with columns from the common dimension tables, not columns from the fact tables.

Extended answer:

Without knowing exactly what you're trying to do when you get your relationship error and what the error says exactly, I can only guess that you're trying to add columns from both your SA_DETAIL and GL_DATA to your pivot at the same time. Regardless of your current relationships, the data model isn't going to be able to accurately match individual rows from these two tables to one another and hence you're getting relationship errors.

For example:

Let's say DEALCODE[Project Name] = "A". This filters both the SA_DETAIL and GL_DATA tables accordingly.

For the sake of example, let's assume that SA_DETAIL[Name] = GL_DATA[Company Name]. This gives you two filtered columns something like this:

SA_DETAIL[Name] = {"Acme Inc", "Acme Inc", "ABC Ltd", "Bubbles Corp"}

GL_DATA[Company Name] = {"Acme Inc", "Coco's", "XYZ Ltd"}

As you can see, just because these tables are related, it doesn't mean that exact row matches can be made between the tables, hence your relationship errors.

What you CAN compare between the two tables is measure values and common dimension table columns. As measures will aggregate all the values in each filtered table into scalar values, these can be compared in the same visual/pivot along with any column(s) from the common dimension tables but, as soon as you add a column from either fact table to the visual/pivot, you'll get your error again.

There's probably a bit more to it than this, but I think this is the principle at play here.

Pete

Hi Anonymous ,

If you want to do specific joins between the tables to get the output you want, you can use Power Query in Excel to do this.

Depending on your Excel version, you should be able to open the Power Query Editor either from here:

...or I think in older versions it sits within the "Combine Queries" option just above.

If you're going down the "Excel as a DB" route, Power Query is going to be a key tool to allow transformations on your tables (such as joins, filters, adding/removing columns etc.). It's another learning curve if you've not used it before I'm afraid, but it's incredibly powerful.

Using Power Query + Data Model/Power Pivot + Measures in Excel basically gives you Power BI capabilities, just with slightly less visually-appealing visualisations. Or you could just download Power BI Desktop if powerful and professional data handling and visualisation is something your organisation is aiming towards.

Re: The actual issue - I think I'd need a small amount of anonymised but representative example data from each table that you want to include in your manipulation, and also an example of what you'd want the output to look like. Once I can physically see what you're going for it should be much easier to understand what needs to be done, and how. However, in my experience, many-to-many joins/relationships or Full Outer joins are very rarely the solution.

Pete