How do I put multiple tables that have the same categories into a single table?

[Loubot3000]

I have three visuals on a page in my report that contain tables with the same categories but different values:

I want to put them all in the same table so that the categories get sorted together.
E.g. if I sort the categories by Unaided Awareness %, then the % columns sort accordingly.

The first visual comes from a table in my database called 'UNAIDEDAWARENESS', which looks like this:

And the values are calculated like this:

% Unaided Awareness = CALCULATE(SUM(Sheet1[Weight]), UNAIDEDAWARENESS[Response] = "Yes") / CALCULATE(SUM(Sheet1[Weight]), UNAIDEDAWARENESS[Response] <> "Missing data")

Sheet1 is just the central table that contains all the weights and uuids and demographics and stuff. All tables are many-to-one related to that central table both ways.

The second and third visual on the page are from a different table in my database, 'FAMILIARITY', which is structured similarly to UNAIDEDAWARENESS:

As you can see, both UNAIDEDAWARENESS and FAMILIARITY have the same categories.

The measures for those two visuals are similar to UNAIDEDAWARENESS but slightly different from eachother:

% Aided Awareness = CALCULATE(SUM(Sheet1[Weight]), FAMILIARITY[Response] = "Heard of it – but am not familiar" || FAMILIARITY[Response] = "A little familiar" || FAMILIARITY[Response] = "Very familiar") / SUM(Sheet1[Weight])

% Familiarity = CALCULATE(SUM(Sheet1[Weight]), (FAMILIARITY[Response] = "A little familiar" || FAMILIARITY[Response] = "Very familiar")) / CALCULATE(SUM(Sheet1[Weight]))

[Prateek97 · Accepted solution]

Hi @Loubot3000 ,

Thanks for clarifying. If you want the 3 visuals to be in the same order always you should try and get all these measures in a same table.

If they are already connected then it should not be a problem. But if the Category is not present in the Central table then you can create a new table by appending the Category column from all the tables and then removing duplicates. 

This would create a Dimension or a Brige table. maybe you can show your value in a single table visual against this category.

[Prateek97]

Hi @Loubot3000 ,

In the Central table which is connected to all the tables you can add an index column, which will have a distinct number against each category.

If the central table has duplicate categories, you can create a new table which will just have All the distinct categories and add then add the Index Column.

Once you load this table, you can get this index column into each table and then sort it by the Index Column using the 'Sort By' option.

Alternatively, if you just sort your 3 visuals by the Category column on the visual itself, that should also be close to your result

[Loubot3000]

The central table does not contain the categories in these tables. They are related via UUID.

Also I think I should've been clearer, it's not that I want a fixed order for the visuals, what I want is for them to have the same order as eachother. So for example, if I sort the categories by Unaided Awareness %, then the others will sort to that same order of categories.

So now I'm thinking about it, I really just want them to all be in the same table.

[Prateek97 · Accepted solution]

Hi @Loubot3000 ,

Thanks for clarifying. If you want the 3 visuals to be in the same order always you should try and get all these measures in a same table.

If they are already connected then it should not be a problem. But if the Category is not present in the Central table then you can create a new table by appending the Category column from all the tables and then removing duplicates. 

This would create a Dimension or a Brige table. maybe you can show your value in a single table visual against this category.

[Loubot3000]

Do you mean create a new table, let's call it C, that has one column for the uuids, a column for the cateogries (they're all the same between tables A and B), a column for the responses from table A, and a column for the responses from table B? And then just create the % measures from that table?

Like a sort of join

[Prateek97]

Yes, that should be the right direction

[Loubot3000]

Okay so I realize that I have to do a full outer join (keeping all rows from both, but matching where possible), to preserve the extra rows that they don't have not in common. I also have to match both the uuid and category - this reduced the loading time significantly (otherwise it would've literally repeated every row for every category instead of matching them).

My issue now is that the table this gives me has seperate uuid and category columns for each question:

What I need is a single column for uuid and a single for category. But also taking into account that both have values that are not in common - so those would form extra rows.

Any idea how to do this?

[Loubot3000 · Accepted solution]

Figured it out. I added a new custom column and entered the following code:

= each if [UNAIDEDAWARENESS.Service] = null then [FAMILIARITY.Service] else if [FAMILIARITY.Service] = null then [UNAIDEDAWARENESS.Service] else [UNAIDEDAWARENESS.Service])

So basically where either column is null, do the value in the other column, and where they match (which is all other cases), just pick the first one (arbitrary).

Then did the same for uuids.

Solved: How do I dynamically merge two columns that contai... - Microsoft Fabric Community

I then formed a relationship between this new table and my central table, made all the measures I needed, using the two response columns that originally came from the two different tables, and put them all in a matrix.

Here's the result:

Now it can all be sorted together, too.

[Loubot3000]

I'm trying to create a new merged table (inner-join) and it hasn't loaded anything in the last hour. It's only like ~40,000 rows. Any idea what to do?

EDIT: oh wait it's unpivoted, so it's more like ~3,000,000 rows lol.