Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@Remco1986 You have to use the new table option and there you paste the code instead of new column.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Awesome, that did the trick!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |