January 21, 2016 at 5:56 pm #3037
I’m working with a data table that contains a multi-select field. Each row of data can have multiple values in the column, and the values are separated by semi-colons. Suppose that the column is called Databases, and sample values look like the attachment.
I want to use the possible values in the field in a slicer and in rows or columns in pivot tables. For example, I would like to have a slicer where I could select “Oracle” to show data only for opportunities that include Oracle. What is the best approach here?
I thought about creating a lookup table with the possible values in an Excel worksheet and importing it into the data model, but I don’t know how I would relate the data table back to the lookup table because there can be multiple values in the column for a given row.
I’ve tried creating a measure for each of the possible values using CALCULATE with filter that looks for a specific value like “Oracle,” but I cannot use measures in a slicer. Also, when I put all of the different measures in the Values area of a pivot, I can see the data, but sorting seems very limited compared to what I would get if I had the values in a lookup table.January 21, 2016 at 6:37 pm #3040
From the start you should know that your concept for a multi-value column and a single value slicer runs uphill against good data model design.
Your model shows signs of a many-to-many relationship between Opportunities and Databases and you need to resolve that upfront.
How many databases are you going to have?
TomJanuary 21, 2016 at 6:41 pm #3041
There are about 20 possible values that can appear in this field, and a single opportunity can have as many as 6 or 7.January 22, 2016 at 3:47 pm #3056
I’m using Power Query to get the data from Salesforce.com, and so far I’m not doing anything to transform the data in this column. Based on Tom’s comment about the data model, it seems like I should be duplicating each row so that there is only one value in the Database column for each row, as in the attached screen shot. Then I could deal with the many-to-many problem separately.
Can anyone offer advice on whether Power Query can do this and if so how? So far the only article I can find on the subject seems to deal with the opposite problem. When using Sharepoint as a source, Power Query was splitting the data out on separate rows, but the user wanted the results that I’m getting instead.
Paul VarleyJanuary 29, 2016 at 4:42 pm #3184
Ken Puls’ blog post on Separating Values and Text helped me to figure this out. I added a custom column in the Power Query editor with the following formula:
The resulting column contains a List, and when I click the Expand Column button in the upper right corner of the column name, it duplicates each row for each value in the list. I’m left with a copy of each row with only one value in the new column, which was the goal.
At first, I wasn’t getting all of the data. I realized this because the first time I loaded the data into the data model, there were fewer rows than there were before expanding the column, which could not be right. It turned out that there were rows in the source data in which the value in the original column was null. The Text.Split function threw an error on the first null and did not go on with the rest of the data. To fix this, I added an earlier step to filter out rows with null in this column. After that, it worked great.January 29, 2016 at 5:46 pm #3187
Thanks for your update and the excellent insight into the Text.Split() function. I have added that to my “Helps” folder (aka “Tips and Tricks”).
Sounds like another case of Power Query to the rescue!
Ken Puls and Miguel Escobar have written an excellent book on Power Query, “M is for (Data) Monkey”. For anyone new to Power Pivot/Power Query who does not have a copy, it is highly (***** out of 5) recommended.
Am interested in how your model, pivots and slicers are working for you. Since there will be a many-to-many relationship between databases and opportunities, you may find a use for DISTINCTCOUNT() in some of your formulas.
TomJanuary 23, 2018 at 12:49 pm #8963
- Started: 0
- Replies: 2
- Total: 2
Could you provide us with you model as I do have the same issue.
/ SørenJanuary 23, 2018 at 5:49 pm #8967
In case pvarley is unavailable, could you describe what you think they saw as the issue and how your experience is different?
It will help others following this post.
TomJanuary 23, 2018 at 6:11 pm #8968
- Started: 0
- Replies: 2
- Total: 2
This is actually a fair reflection.
I had the exact same issue, but actually managed to handle it with the solution suggested from pvarley. The situation is as follow. The different selection-values can be found in column “Spor” in Problemlog.
The solution was to create Problemlog2 in this PBI-file.January 24, 2018 at 3:51 am #8973
I am sure that when pvarley returns to this post, they will be glad to hear their idea helped you.
You must be logged in to reply to this topic.