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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

5 REPLIES 5
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.