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.
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
Solved! Go to Solution.
@Anonymous ,
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.
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!)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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?
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:
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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?
@Anonymous ,
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.
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)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@Anonymous , See if this can help
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |