Forum Discussion
How do I put multiple tables that have the same categories into a single table?
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:
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:
Hi Anonymous ,
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.
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.
8 Replies
Prateek97Resolver III
Hi Anonymous ,
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
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
Hi Anonymous ,
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.