October 19, 2018 at 8:25 pm #11008
We have a table something like the following: level (item, style, or prod) , id (item#, style# or prod#), attr1, attr2, attr3, attr4 , attr5, amt / $.
user selects the level they want from a filter or slicer.
We want to have a single pivot where a user can select , say “*flannel*” from an attr drop down page or row filter. And then what I want it to do is look for *flannel* in the any of the 5 attr fields, and display those rows within the level they selected.
I have looked into creating a reference table with level, id, attr that contains the union of level, id, and attr for all 5 attrs, but if this table were joined to the main fact table it would 5x the amt/$ per level. So, I am wondering of the best approach within PowerPivot to make this happen? I was thinking of potentially concatenating all of the attrs together into one big field so when the contains filter were applied it would see flannel, but I think it might exceed the column size.October 19, 2018 at 8:46 pm #11010
If I am picturing your data correctly, you could keep your five attribute columns, but create one more (hidden from report view) that is the concatenation you describe. Your measure could filter on that concatenated column and return a “Yes” when found, blank when not (returning “Yes” would make a row visible, but returning blank would not).
If I am not picturing correctly, please put up some sample data.
TomOctober 22, 2018 at 1:37 pm #11012
Thanks Tom. Yes, you are picturing it correctly. Would this Yes, when found, blank when not be something done using something like Robs “ContainsX” function ( https://powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-table-2/ ) where you would need some sort of keyword table? Thanks again.October 22, 2018 at 3:02 pm #11013
I was thinking something more similar to one of the commenter’s responses (Sasha Juric):
FILTER ( Matchlist, SEARCH ( Matchlist[Keyword], Companies[Companies], 1, 0 ) )
Also, much depends on the attributes: are they all variations of the same object? (like color or model or position, etc). If they are, the formula may still be useful, but your model would probably need to change to accommodate cases where not all attributes are populated or you need to add additional attributes later).October 22, 2018 at 4:46 pm #11016
I am having difficulty understanding how to implement concatenatex for this scenario. I think I might be trying to use a hammer here. This is what I was thinking I could do.
In my test spreadsheet. I created a list of attributes say (flannel, twill, denim, etc..),
when the user selects one of those attributes, then plan is to use VBA to update the keyword with the value they selected (flannel – for example ) and then refresh the pivot. The column with the DAX ContainsX would execute against the refreshed pivot and only the rows that have a value matching “flannel” would get a “Y”; therefore, the pivot (filtered to columns with a value of “Y”) would only show me flannel rows. However, it feels like I might be adding more steps here than are really needed or overcomplicating this a bit. Is there an easier way to do this?October 22, 2018 at 8:00 pm #11017
Did you mean to attach a spreadsheet?
Also, when I was thinking of concatenating, I do not recall thinking of CONCATENATEX, but believe I was considering the concatenate operator (&).
VBA is unnecessary.
If you haven’t something to attach, please take the time to do so (about 5 or so rows would probably be enough) and we could think through this with examples instead of theory.October 22, 2018 at 8:25 pm #11018
Yes. I did, but it must have had an issue at upload. Thank you for your reply. I am going to try attaching it again. There is no VBA in it, it just has a sample table the calculation to determine the “Y” or blank and a pivot. Basically, just my thoughts as I am trying to work this through. Thank you so much for taking a look.
Attachments:You must be logged in to view attached files.October 22, 2018 at 8:51 pm #11020
What is your comfort level with Power Query?October 22, 2018 at 10:23 pm #11021
Let’s start with the attached workbook and then modify as needed.
It is based on a match (not a search) of a user selection. The measure allows the user to select one OR more slicer filters, and if any one of the selected attributes belongs to the product/item, then that product/item will appear in the pivot. Possible also to create another measure where products must match all selected criteria.
Attachments:You must be logged in to view attached files.October 23, 2018 at 2:36 pm #11026
This is excellent. Thanks.
DanOctober 23, 2018 at 3:21 pm #11027
Thanks for the kind words.
It was an interesting challenge. Please feel free to ask any questions.
TomOctober 23, 2018 at 3:25 pm #11028
Great. Thank you for your time. Always pressing against what the limits are of Power Pivot here at work.October 23, 2018 at 3:31 pm #11029
To reduce the file size you could eliminate the concatenated calculated column. It could even be replaced by a measure which would not impact your file size at all.
Thanks for the constructive comments. Developing solutions is an iterative process, each step getting better.
Rob Collie picks up the tab for my work in the forums. However, I am always open to outside consulting opportunities.
TomOctober 23, 2018 at 5:07 pm #11031
I made a Staging table against my actual dataset on the sheet (connection only), formatted all of the attribute columns and the Product column (called “Product_Memb_ID”) as text. Referenced this query to create the Product_Attributes table and then referenced the Product_Attributes query to create the Attribute table in the model. And then when I try to add the naturalinnerjoin Has At Least One of the Selected Attributes:=IF ( COUNTROWS ( NATURALINNERJOIN ( Attributes, Product_Attributes ) ) > 0, 1, BLANK() ) it tells me no common join expression detected. Product Attributes: contains columns PRODUCT_MEMB_ID, Attribute Header, and Attribute. Atributes: contains column Attribute. Based upon the tables it looks to me that the natural innerjoin should not be throwing an error. Ever experience this type of error before? And how did you fix it?
DanOctober 23, 2018 at 5:20 pm #11032
never mind. I forgot to relate the tables. apparently with a natural inner join you have to have the relationship created in the datamodel, and what it looks like is the natural inner join function must override the left outer join. It is working now.
You must be logged in to reply to this topic.