cancel
Showing results for
Did you mean:
Highlighted
Helper IV

Best way to merge similar data from different sources

Hey all,

PBI Noob here again.

My next issue is I have 3 or 4 systems that capture the "same" data differently. More specifically for me its slight difference in location names. Example..

 System 1 System 2 System 3 Location Location Location Shed 231 North West Sydney Shed 231 Art Gallery Shed ThePickleShop Pickle Shop 8453 - The Pickle Shop - 544

If I wanted to manipulate/filter data based on "Shed 231" is it better to create multiple measures and then join them into one larger measure? Or is there a way to make PBI understand that North West Sydney Shed 231 = Shed 231 & Art Gallery Shed = Shed 231?

Cheers,

J

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

Create measure using dax like pattern below:

``````Count Shed 231 =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( Table1, CONTAINS ( Table1, Table1[System1], "Shed 231" ) )
)
+ CALCULATE (
COUNTROWS ( Table2 ),
FILTER ( Table2, CONTAINS ( Table2, Table2[System2], "Shed 231" ) )
)
+ CALCULATE (
COUNTROWS ( Table3 ),
FILTER ( Table3, CONTAINS ( Table3, Table3[System3], "Shed 231" ) )
)

Count The Pickle Shop =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
CONTAINS ( Table1, Table1[System1], "The" )
&& CONTAINS ( Table1, Table[System1], "Pickle" )
&& CONTAINS ( Table1, Table[System1], "Shop" )
)
)
+ CALCULATE (
COUNTROWS ( Table2 ),
FILTER (
Table2,
CONTAINS ( Table2, Table2[System2], "The" )
&& CONTAINS ( Table2, Table2[System2], "Pickle" )
&& CONTAINS ( Table2, Table[System2], "Shop" )
)
)
+ CALCULATE (
COUNTROWS ( Table3 ),
FILTER (
Table3,
CONTAINS ( Table3, Table3[System3], "The" )
&& CONTAINS ( Table3, Table3[System3], "Pickle" )
&& CONTAINS ( Table3, Table[System3], "Shop" )
)
)
``````

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 REPLIES 7
Highlighted
Super User IV

@Jaydpie , See if this can help

Proud to be a Super User!

Highlighted
Super User I

I'd also check out the fuzzy match on the merge, I used it to solve a similar problem recently.

If you have the sources in 3 different tables, you could create duplicates of each with just the locations.  Then merge with fuzzy match into a new table. That table will have three columns matching all the locations. You can bring that table into your data model, then connect up the three source tables to their matching location column. (Hide the three duplicates with only locations).  Now you can use any of the columns as a slicer in your report.

The one that was tricky with the example you gave was ThePickleShop -- it wasn't able to match until I added spaces before the capitals.

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2019-feature-summary/ (it is explained a fair way down in the blog post!)

Highlighted
Helper IV

Hey Thanks for that!

I think this is probably close to what I need! What about if I needed to write a measure? If I chose 1 or any of the 3 options would they then apply to everything associated?

Highlighted
Super User I

If you just choose one of the columns to be your slicer, it would include anything associated.

The relationships just need to be defined:

then you can use any of the Merge1 columns to slice the other 3:

Highlighted
Helper IV

Ok cool,

So what I want/need as will is this...

Measure = COUNTROWS(Table[location])

 System 1 System 2 System 3 Location Location Location Shed 231 NW Sydney Shed 231 Art Gallery Shed Shed 231 Pickle Shop 8453 - The Pickle Shop - 544 ThePickleShop NW Sydney Shed 231 8453 - The Pickle Shop - 544 ThePickleShop NW Sydney Shed 231 8453 - The Pickle Shop - 544 Shed 231 NW Sydney Shed 231 8453 - The Pickle Shop - 544 ThePickleShop NW Sydney Shed 231 Art Gallery Shed

So if that was my 3 different data sets (not matched or merged/fuzzy)

I would need the output to be something like

Shed 231 = 10

ThePickleShop = 8

That makes sense?

Highlighted
Super User I

If each system was in it's own table, you could do a measure that summed a countrows for each table. Then use one of the locations in the merged table for each location:

All Rows = countrows(System1) + countrows(System2) + countrows(System3)

Highlighted
Community Support

Create measure using dax like pattern below:

``````Count Shed 231 =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( Table1, CONTAINS ( Table1, Table1[System1], "Shed 231" ) )
)
+ CALCULATE (
COUNTROWS ( Table2 ),
FILTER ( Table2, CONTAINS ( Table2, Table2[System2], "Shed 231" ) )
)
+ CALCULATE (
COUNTROWS ( Table3 ),
FILTER ( Table3, CONTAINS ( Table3, Table3[System3], "Shed 231" ) )
)

Count The Pickle Shop =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
CONTAINS ( Table1, Table1[System1], "The" )
&& CONTAINS ( Table1, Table[System1], "Pickle" )
&& CONTAINS ( Table1, Table[System1], "Shop" )
)
)
+ CALCULATE (
COUNTROWS ( Table2 ),
FILTER (
Table2,
CONTAINS ( Table2, Table2[System2], "The" )
&& CONTAINS ( Table2, Table2[System2], "Pickle" )
&& CONTAINS ( Table2, Table[System2], "Shop" )
)
)
+ CALCULATE (
COUNTROWS ( Table3 ),
FILTER (
Table3,
CONTAINS ( Table3, Table3[System3], "The" )
&& CONTAINS ( Table3, Table3[System3], "Pickle" )
&& CONTAINS ( Table3, Table[System3], "Shop" )
)
)
``````

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors