Get distinct values from multiple tables to use in filter

[Remco1986]

Hi all, I've not been able to get this working, even after trying some results from this forum.

I have a report with multiple datasets that are not related. However, there are some columns that are similair, so I would like to create a custom column with ALL values from both tables, to use in a filter.

Example, I have a set with phone statistics that includes a column 'Country'. Countries present (with duplicates on row level) are 'Netherlands' and 'Belgium'. 

Then I have a survey results dataset that also has the column 'Country'. In this data we have the countries 'Netherlands' and 'France'.

I would like to create a custom column that I can use in a filter/slicer that shows the values 'Netherlands', 'Belgium' and 'France'.

Next, I want to use this column to create a relationship with the two tables. So when I select the filter option 'Netherlands', I can see the voice stats and survey results for this country. When I select 'France' I will only see survey results and no phone stats.

Ofcourse this can be done in the 'Get Data' part by getting the Country column from each data-set, append it and remove duplicates. But I'm wondering if this can be done in a normal New Column in the report view itself.

[Ray_Minds]

Hi @Remco1986 
Create New table #Combine Distinct Values =DISTINCT(UNION(DISTINCT('Balance Sheet Data'[Balance Sheet  Type]),DISTINCT('Balance Sheet Data'[Category]),DISTINCT('Balance Sheet Data'[Subcategory])))


If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[negi007]

@Remco1986 

You can create a new table and create a column like below. Below table is extracting values from 4 different columns in 4 different tables and then extracting unique values from it. Change the code as per your data.

Supplier = FILTER (

DISTINCT ( UNION ( VALUES ( Tab1[Supplier] ), VALUES ( Tab2[Supplier] ),VALUES ( Tab3[Supplier] ),VALUES ( Tab4[Supplier]) ) ),

[Supplier] <> BLANK ()

)

[Remco1986]

A follow-up on this one. Can I use this method to create a new table with two columns?

E.g. I want the Employee Name and Employee ID columns from different tables

[Remco1986]

Thanks, I found this similar code, however it's returning 'A table of multiple values was supplied where a single value was expected.' which is basically what I'm trying to do. Combining multiple columns with duplicate values into one column with distinct values.

I only changed the column looking as per below:

Country = FILTER(
    DISTINCT(UNION(VALUES('4 - Data (CSAT)'[Country]), VALUES('4 - Data (Voice)'[Country]))),
    [Country] <> BLANK()
)

[negi007 · Accepted solution]

@Remco1986  You have to use the new table option and there you paste the code instead of new column. 

[Remco1986]

Awesome, that did the trick!