Home Forums Power Pivot Is this type of filtering possible?

This topic contains 18 replies, has 2 voices, and was last updated by  dfish 3 weeks, 2 days ago.

Viewing 15 posts - 1 through 15 (of 19 total)
  • Author
    Posts
  • #11008

    dfish
    Participant
    • Started: 3
    • Replies: 13
    • Total: 16

    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.

     

    #11010

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Dan,

    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.

    Tom

    #11012

    dfish
    Participant
    • Started: 3
    • Replies: 13
    • Total: 16

    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.

    #11013

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Dan,

    Excellent reference.

    I was thinking something more similar to one of the commenter’s responses (Sasha Juric):

    ContainsX :=
    IF (
    COUNTROWS (
    FILTER ( Matchlist, SEARCH ( Matchlist[Keyword], Companies[Companies], 1, 0 ) )
    ),
    “YES!”,
    “Probably Not”
    )

    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).

    #11016

    dfish
    Participant
    • Started: 3
    • Replies: 13
    • Total: 16

    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?

    #11017

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Dan,

    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.

    #11018

    dfish
    Participant
    • Started: 3
    • Replies: 13
    • Total: 16

    Hi Tom,

    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.

    Best,

    Dan

    Attachments:
    You must be logged in to view attached files.
    #11020

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Dan,

    What is your comfort level with Power Query?

    #11021

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Dan,

    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.

    Tom

    Attachments:
    You must be logged in to view attached files.
    #11026

    dfish
    Participant
    • Started: 3
    • Replies: 13
    • Total: 16

    Hi Tom,

    This is excellent. Thanks.

    Best,

    Dan

    #11027

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Dan,

    Thanks for the kind words.

    It was an interesting challenge. Please feel free to ask any questions.

    Tom

    #11028

    dfish
    Participant
    • Started: 3
    • Replies: 13
    • Total: 16

    Great. Thank you for your time. Always pressing against what the limits are of Power Pivot here at work.

    #11029

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Dan,

    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.

    Tom

    #11031

    dfish
    Participant
    • Started: 3
    • Replies: 13
    • Total: 16

    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?

    Thanks,

    Dan

    #11032

    dfish
    Participant
    • Started: 3
    • Replies: 13
    • Total: 16

    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.

     

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic.