The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |