Merged Queries

[Anonymous]

Hi All,

I need to understand how to use merged queries so that only 1 column is used to display data from 2 tables.

For ex Table1.VendorID merged with Table2.VendorID with full outer join.

If I use Table1.VendorID in Table visual it would show all data from Table1 but only matching data form Table2 and other way if I select Table2.VendorID

How can I use vendor column in report so that all data from Table1 and Table2 is displayed irrespective data is avialble or not?

Any thought here please.

Thanks

[Anonymous]

Hi @Anonymous,

Perhaps you can try to create a calculated table to union two table key column values and add custom column fields to lookup correspond field values from two tables based on the key field.
Regards,

Xiaoxin Sheng

[darentengmfs]

Hi @Anonymous 

Create a new column in either DAX or M.

Given that you name your first Vendor ID column as VendorIDTable1 and the second Vendor ID column VendorIDTable2:

In DAX, use this formula:

VendorID-All = IF(ISBLANK([VendorIDTable1]),[VendorIDTable2],[VendorIDTable1])

In M, use this formula:

VendorID-All = if [VendorIDTable1] = null then [VendorIDTable2] else [VendorIDTable1]

These 2 formulas should fill up the Vendor IDs.

[AnkitKukreja]

Hi @Anonymous 

It should not be very tough to crack, please share your pbix file after deleting any pii data.

I can have a look at it and will let you know about the solution for the same.

Thanks,
Ankit Kukreja

LinkedIn