I have tried looking for the solution, and im sure its out there, I just dont kow the buzz words to find it so am coming to you gusy for some help and to try explain my issue.
This is currently in one dataset, which I am effectively trying to make a venn diagram for. The idea is to find similar shops/tenants between different shopping centres.
The data set includes a list of the stores, and in a seperate column the shopping centre it is in.
I am trying to make a dynamic filter, so that I can find the shops that are similar between to shopping centres, as well as the shops that are unique to each centre.
Currently have made unique IDs for each unit/centre combo, but then when I slice it, I am unable to get the "similar colum" to show any data. For example, if data set looks as follows:
Centre 1, Shop 1
Centre 1, Shop 2
Centre 1, Shop 3
Centre 2, Shop 2
Centre 2, Shop 4
I want the results to show:
Unique centre 1: Shop 1, Shop 3
Similar: Shop 2
Unique centre 4: Shop 4.
My issue is that even though the shop name is the same between centres, because they are in different centres I cant get them to figure out they are equal.
Unsure if I have been clear or not, but would love some help from an experienced user. Happy to provide more information if someone is able to help me.
Thank you very much
It appears this could be solved in M and DAX. For the DAX solution you will have to look into creating caclualted tables for each Center. Here is one of the way to do this.
// similar CALCULATETABLE ( SUMMARIZE ( 'Table', 'Table'[Center], 'Table'[Shop] ), INTERSECT ( SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[Center] = "Centre 1" ), "S1", 'Table'[Shop] ), SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[Center] = "Centre 2" ), "S2", 'Table'[Shop] ) ) ) // unique EVALUATE CALCULATETABLE ( SUMMARIZE ( 'Table', 'Table'[Center], 'Table'[Shop] ), EXCEPT ( SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[Center] = "Centre 1" ), "S1", 'Table'[Shop] ), SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[Center] = "Centre 2" ), "S2", 'Table'[Shop] ) ) )
There is certainly ways to solve this. First thing we need to make sure is that when you want 2 names to be equal, are they actually the same in the data?
Numbers are easy. 3 always equal 3. But when you are working with Text. "3" doesn't equal "three" which doesn't equal "Three" which doesn't equal "three (3)" and so on. The largest challenge of working with Text is aligning your data. You may have to go through a process of data cleansing to line your data up. This might involve changing all of the text to lower case for comparisons.
Once you know you have the ability to compare. Now you need to consider what properties you want to calculate for each item. Some properties you might want to store inside the data, other properties you might want to calculate on the fly. Break down each "check" into the smallest principles possible.
For example. Lets say you wanted to know if a name is unique. You could create a calculated column that does a count of any other line with the same name. This will be stored line by line in your data. From here you could simply filter on that column for any line that has the number 1.
Thank you very much for your response.
Is there any way to make this dynamic? Ie, so that I can use a slicer to change teh comparrison from Centre 1 to Centre 3 with a slicer.
Again, thanks for your help