Merge two same columns from twice imported tables in table visual

[CharlieN]

Hi,

I wonder how I can merge the values of two columns from my table visual.

Actually I get the values I need by importing two of the same tables since the path to get the ship name - 'schip (via rapport)' and 'schip (niet via rapport)' - is different and apparently I don't find another way (by using the 'combined_ship' table only once) to retrieve the names for both cases - one found via the 'braboejob report' table, and the other one via the 'braboejob combined_order' table.

So, if the data model can stay like this, how do I bring the values of the two last columns of the table visual together?
Or is a revision of the table relationship needed? If so, how?

Link to pbix -> https://we.tl/t-FoMjON3ZSv or https://www.icloud.com/iclouddrive/080W555GyS0u27fhPPcyKv3mw#ejob%5Fincidents%5Ffindshipname

Thanks already for your input.

KR,

CharlieN

[ABD128]

Hi @CharlieN 

Instead of relying on two physical imports of the same table or building an ambiguous relationship web, create a new logical context table that combines ship IDs with their source (rapport or non-rapport), and use that to resolve name lookups cleanly.

Create a Ship Reference Table with Source Context Use DAX to generate a unified ship reference with context: ShipContext =

UNION(

SELECTCOLUMNS(

'schip (via rapport)',

"ShipID", [ShipID],

"ShipName", [ShipName],

"Source", "ViaRapport" ),

SELECTCOLUMNS( '

schip (niet via rapport)',

"ShipID", [ShipID],

"ShipName", [ShipName],

"Source", "NietViaRapport" ) )

In both braboejob_report and braboejob_combined_order, add a calculated column called "Source" with hardcoded values:

In braboejob_report :Source = "ViaRapport"

In braboejob_combined_order: Source = "NietViaRapport"

Use a Measure for Dynamic Ship Name Resolution Create a measure that filters the Ship Context table based on both Ship ID and Source from the current row context of your visual:

Resolved Ship Name =

CALCULATE(

MAX(ShipContext[ShipName]),

FILTER(

ShipContext,

ShipContext[ShipID] = SELECTEDVALUE(FactTable[ShipID])

&& ShipContext[Source] = SELECTEDVALUE(FactTable[Source])

)

)

[CharlieN]

Hi @ABD128,

SELECTCOLUMNS( '

schip (niet via rapport)',

"ShipID", [ShipID],

"ShipName", [ShipName],

"Source", "NietViaRapport" ) )

The bold italic part from the above implies it's a column from the same table, isn't it? Actually there is only one name column, I renamed it to 'schip (niet via rapport)' for the secondly imported combined_ship table.. So we're still working with two of the same tables then?

So the 'name' column I renamed to 'schip (via rapport)' for table 'braboejob combined_ship', and to 'schip (niet via rapport)' for table 'braboejob combined_ship (2)'...

Your input is much appreciated, thank you.

KR,

Charles

[ABD128]

Hi @CharlieN 

The issue arises from utilizing two separate imports of the same combined_ship table, renamed as schip (via rapport) and schip (niet via rapport), each linked to different segments of the data model. This configuration leads to redundancy and ambiguity in relationships, complicating the accurate resolution of ship names in visuals.

The optimal solution is to eliminate the duplication by importing the combined_ship table only once. This single import should serve as a central lookup table containing all unique Ship IDs and their names. Subsequently, establish relationships from both braboejob_report[ShipID] and braboejob_combined_order[ShipID] to the unified combined_ship[ShipID] table.

As Power BI permits only one active relationship between tables, one relationship should remain inactive. To resolve ship names contextually within visuals, a DAX measure utilizing the USERELATIONSHIP function can dynamically activate the appropriate relationship based on whether the visual uses data from the report or the order table. The measure employs CALCULATE with USERELATIONSHIP for both tables and returns the first available ship name using COALESCE.

This approach maintains a clean data model, avoids redundancy, and ensures precise ship name resolution without requiring duplicate tables or ambiguous joins. Optionally, for enhanced simplicity, the user can merge the two fact tables (braboejob_report and braboejob_combined_order) in Power Query and add a source indicator column, further streamlining reporting and DAX calculations.

[CharlieN]

Hi @ABD128,

I'm trying the Power Query route here, only not clear to me how to add such a source indicator column..
I get the concept that I have to choose the ship whether it's coming via the report table, or via the combined_order table, but how to exactly introduce such a column in the merged table? From there on I can continue my quest, I hope...

Sorry, just a (DAX & M) rookie here.

Thanks again,

KR,

CharlieN

[v-karpurapud]

Hi @CharlieN 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @CharlieN 

We are following up to see if you have had the chance to review the information provided. If you have any further questions, please do not hesitate to contact us. Could you confirm whether your query has been resolved? If so, kindly mark the helpful response and accept it as the solution to assist other community members in resolving similar issues more efficiently. If not, please provide detailed information so we can better assist you.

Thank You.

[CharlieN]

Hi @v-karpurapud,

Unfortunately none of the possible solutions from @ABD128 and @m4ni worked for me.

Since I can not link the newly created table 'ShipLookup' with both of the original combined_ship tables (because of ambiguous paths), in my attempt only ships are shown (last column 'Naam schip' in table visual) from 'combined_ship (2)', the table which has an active relationship with 'ShipLookup'...
And the COALESCE function doesn't return anything -> 'Merged Ship Name Col'
Also merging ('Merge Queries?) the two original tables in PQ (into 'braboejob_combined_ship') and merging both columns (with only 'schip (niet via rapport)' kept from merged table; renamed to 'schip (niet via rapport) II'). It returns the same ship names as the ones retrieved from table 'braboejob_combined_ship'.

I probably should start over without importing those two tables (combined_port_service & combined_ship) twice, but hard to find out for me...

Updated pbix.

KR,

CharlieN

[ABD128]

Hello @CharlieN 

To resolve the issue of merging two columns containing ship names from duplicate imports of the same table in your Power BI model, a more optimal and scalable approach would be to create a disconnected bridge (lookup) table that consolidates all unique ship names. This can be achieved by creating a new table using the DAX expression:

ShipLookup = DISTINCT(UNION(SELECTCOLUMNS('schip (via rapport)', "ShipName", [ShipName]), SELECTCOLUMNS('schip (niet via rapport)', "ShipName", [ShipName])))

Optionally, you may define inactive relationships between this ShipLookup table and both original ship tables if a consistent key exists. Afterward, you can define a DAX measure that dynamically selects the available ship name based on the context of the current visual:

Merged Ship Name =

VAR FromRapport = SELECTEDVALUE('schip (via rapport)'[ShipName])

VA FromNietRapport = SELECTEDVALUE('schip (niet via rapport)'[ShipName])

RETURN COALESCE(FromRapport, FromNietRapport)

Use this measure in your table visual instead of adding both individual columns. This method avoids table duplication, keeps your data model clean and efficient, and ensures the ship name displayed is always contextually accurate based on which data path is providing the value.

[m4ni]

Hi @CharlieN 

As a suggestion -  I would look to merge the braboejob combined_ship and braboejob combined_ship (2) table in PowerQuery using the id column since that is your relationship - which is actually disabled.

Once the tables are merged I would only expose the schip (neit via rapport) column from the second table and bring it into the first table.  From here you can merge the 2 columns together within the same table using a COALESCE maybe depending on your logic for merging, and use the new column on your table visual.

Either way using 2 identicial tables in a model is not recommended and would make thing harder later down the road.

Let me know if you need further details or if this works.

[CharlieN]

OK @burakkaragoz, thanks for your quick reply, I will have a look at it.

In the meanwhile I added the link (on two platforms to be sure it will be downloadable for a while) to the pbix in the original post, since that might be of added value to analyse - and there is no way to add an attachment here?

KR,

CharlieN

[burakkaragoz]

Hi @CharlieN ,

To merge the values from two columns that come from twice-imported versions of the same table in your table visual, you have a couple of options depending on your data model and your needs:

1. Create a Calculated Column (DAX)

If the columns you want to merge are in the same table visual but come from different tables (e.g., [ShipName1] from Table1 and [ShipName2] from Table2), you can create a calculated column or a DAX measure in Power BI that merges these two columns:

If you want to show the first non-blank value:

DAX

MergedShipName = 
IF(
    NOT(ISBLANK(Table1[ShipName1])),
    Table1[ShipName1],
    Table2[ShipName2]
)

You can adapt this logic based on which value you want to prioritize.

2. Use Power Query to Merge Before Loading

If possible, merge the two tables in Power Query before they are loaded into the model. You can use “Append Queries” to stack the two tables or “Merge Queries” to join on a common key, and then create a single column.

3. Adjust Relationships

If the only way to get the names is to keep both tables, make sure your relationships are set correctly. You could create a new table (e.g., Combined_Ships) with unique Ship names and link both tables to this one via relationships. Then, you can always pull the ship name from the central table, avoiding duplicate columns.

4. In the Table Visual

If you must display both columns in the same visual and want to visually merge them, you can add both columns to the table and use a DAX measure or calculated column (as above) to display a single merged value.

---

Summary:

• Use a DAX calculated column or measure to merge the columns in your visual.
• For a more robust solution, consider restructuring your data model so both tables reference a single ship name lookup table.
• Power Query can also help if you want to merge the tables before loading.

If you describe more about your data model or share a sample, I can give a more targeted solution. Let me know if you need a step-by-step guide for any approach!

[CharlieN]

Hi @burakkaragoz,

For the moment I'm not getting there.. Since the ship names are not unique (the id's are) it says for the DAX calculated column...

Also merging in PQ doesn't give me the expected result, my visual doesn't seem to pick-up the relationship with the newly added (combined) table - because it does not know which one of the two with same id to pick I presume...

In essence a ship (from table 'combined_ship') can appear multiple times with the same name, but each occurence has a different id.

Updated pbix: https://www.icloud.com/iclouddrive/0af-fAQKBFSebshgXYUQxNNog#ejob%5Fincidents%5Ffindshipname_2

Thanks again for your input and help.

KR,

CharlieN