Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Remco1986
Helper I
Helper I

Get distinct values from multiple tables to use in filter

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.

1 ACCEPTED SOLUTION

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

 

negi007_0-1603811176562.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

View solution in original post

6 REPLIES 6
Ray_Minds
Responsive Resident
Responsive Resident

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
Community Champion
Community Champion

@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 ()
)



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

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

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()
)

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

 

negi007_0-1603811176562.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

Awesome, that did the trick! 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.