Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have serial numbers and locations in two different tables. I want to compare the datasets by putting the data side-by-side to show for each serial number, which location each dataset shows for each serial number.
Like this:
Serial Number | Dataset 1 | Dataset 2 |
1234 | Location 1 | Location 1 |
5678 | Location 2 | Location 5 |
What I'd like to do is create a calculated table that unions the serial numbers from both datasets and removes duplicates. Then I can join that table to both datasets and create a grid that returns all serial numbers from the calculated table with the first location from Dataset 1 and the first location from Dataset 2.
But my formula isn't getting there:
Serial Numbers = DISTINCT(SUMMARIZE('Dataset 1','Dataset 1'[Serial #]) & SUMMARIZE('Dataset 2','Dataset 2'[Serial #]))
Any ideas? Or is there a better way to get the result I'm going for? (A couple of complications... there are instances of duplicated serial numbers in both datasets, and both datasets have serial numbers that aren't in the other dataset.)
Solved! Go to Solution.
Hi @Anonymous,
I agree with @cthurston, this is easier to accomplish in Power Query. If you don't want to go that route, I answered a similar question just an hour or so ago. I am not completely satisfied that I arrived at the most elegant way to accomplish this but in case you want to have a look, check out my response at the end of this thread.
http://community.powerbi.com/t5/Desktop/Connecting-Two-Tables-w-Lookup/td-p/276284/page/2
Actually I found that what I was missing was the UNION function. Creating this calculated table gave me just what I needed:
Serial Numbers = DISTINCT( UNION( SUMMARIZE( 'Dataset 1', 'Dataset 1'[Serial #], ), SUMMARIZE( 'Dataset 2', 'Dataset 2'[Serial #], ) ) )
Then I was able to join this calculated table to each of the original tables and pull in location, using IFERROR to handle cases with more than one match:
Dateset 1 Location = IFERROR( LOOKUPVALUE( 'Dataset 1'[Location], 'Dataset 1'[Serial #], 'Serial Numbers'[Serial #] ), "Multiple" )
great solution...and quick too.
Actually I found that what I was missing was the UNION function. Creating this calculated table gave me just what I needed:
Serial Numbers = DISTINCT( UNION( SUMMARIZE( 'Dataset 1', 'Dataset 1'[Serial #], ), SUMMARIZE( 'Dataset 2', 'Dataset 2'[Serial #], ) ) )
Then I was able to join this calculated table to each of the original tables and pull in location, using IFERROR to handle cases with more than one match:
Dateset 1 Location = IFERROR( LOOKUPVALUE( 'Dataset 1'[Location], 'Dataset 1'[Serial #], 'Serial Numbers'[Serial #] ), "Multiple" )
Hi @Anonymous,
I agree with @cthurston, this is easier to accomplish in Power Query. If you don't want to go that route, I answered a similar question just an hour or so ago. I am not completely satisfied that I arrived at the most elegant way to accomplish this but in case you want to have a look, check out my response at the end of this thread.
http://community.powerbi.com/t5/Desktop/Connecting-Two-Tables-w-Lookup/td-p/276284/page/2
Within the query editor you can merge queries as new using an inner join.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |