Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Budfudder
Helper IV
Helper IV

Reverse Contains?

I have a table containing listings of of our sales, and each row contains (among other data) the product(s) sold in that sale.

 

I have a slicer containing various types of products. The idea is that the table will only show those sales that include one or more of the products selected in the slicer.

 

But the product names are many, and the product types are few. For example, here are some sample product names:

 

Dining Table (6)
Dining Table (8)
Card Table
Coffee Table (Pine)
Bedside Table
Chair (Wooden)
Chair (Plastic)
Collapsible Chair
Wardrobe (Wood)
Wardrobe (Built-In)
Wardrobe (Small)
Couch (Leather)
Couch (Fabric)

One of the values selectable in the slicer is 'Table'. So if the user chooses 'Table', I want to show all of the values in the table above which contain the string 'table'. I'm using a simple search to see if products should be shown - if the slicer value is found in the product name, then I show sales featuring that product. Great.

 

But if the user chooses multiple values in the slicer, I'm in trouble. For example, they might choose 'Table' and 'Chair'. In that case, I want to show all sales featuring products that contain either the string 'Table' or the string 'Chair'.

 

I can get all of the selected values in the slicer using CONCATENATEX, but there's no way using DAX that I can iterate through them, checking (for each row in the database table) whether each of the selected values can be found within the product name.

 

I thought of creating a table at runtime with the values selected in the slicer - but even if I do so, I can't use 'CONTAINS' because it goes the wrong way - I want to search for each row in the table in a single product name, rather than search for a single product name in each row in the table.

 

Thanks for any help.

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

If you have a table that contains your product types (not related to your product table), then you can write a measure that checks if the product matches any of the selected ones:

 

Visible = 
MAXX(
    VALUES(ProductTypes[Type]),
    MAXX(Products,
        IF(
            SEARCH([Type], Products[Product], 1, 0) > 0,
            1,
            0
        )
    )
)

OrLogic2.png

Then just set the visual level filter to only show [Visible] = 1.

View solution in original post

13 REPLIES 13
AlexisOlson
Super User
Super User

If you have a table that contains your product types (not related to your product table), then you can write a measure that checks if the product matches any of the selected ones:

 

Visible = 
MAXX(
    VALUES(ProductTypes[Type]),
    MAXX(Products,
        IF(
            SEARCH([Type], Products[Product], 1, 0) > 0,
            1,
            0
        )
    )
)

OrLogic2.png

Then just set the visual level filter to only show [Visible] = 1.

Thank you, Alexis - genius! Now I've just got to work out why and how it works 🙂

Sorry, I should have explained it better.

 

Let's go from the inside out:

 

  • The SEARCH function will return the position of the Type in the Product string if it finds it (returns 0 otherwise).
  • If the position is greater than 0, then Type is a substring of Product and the IF function returns 1 (0 otherwise).
  • The inner MAXX iterates through the list of products and the IF gives 1 for all the ones that match the current Type. (Within the filter context of the table, this list will only be the with the current product.)
  • The outer MAXX is iterating through each product type that has been selected with the slicer and checks for each Type whether that string is a substring of Product using the inner MAXX. If any of the Types selected returns a 1, then the max over all the types is 1, just like OR logic.

If the inner MAXX part doesn't make complete sense, that's OK. I accidentally made it more complicated than it needed to be. Here's a slightly simpler version that's a bit easier to follow.

 

Visible = 
VAR CurrentProduct = SELECTEDVALUE(Products[Product])
RETURN MAXX(VALUES(ProductTypes[Type]), IF(SEARCH([Type], CurrentProduct, 1, 0) > 0, 1, 0))

Hi Alexis, it doesn't appear that your shorter version works...at least, it doesn't return the same result as the longer version. And (confirmed by checking with Advanced Find) the longer version is correct.

Interesting. I must not have been crazy to put it in there in the first place.

 

Is it different just for subtotals that represent multiple products or are there other cases as well?

Others as well - it just doesn't show all products matching the selection.

That's odd. I'm not seeing any issues using the sample data you gave. Is there an example you can give where the two give different results?

I'm trying - first I need to understand both versions. In both the original and the newer version, you reference a field [Type] as the first argument in the SEARCH function. What field is that, and why didn't you have to qualify it?

When you operate with the row context of a table, the columns of that table aren't required to be fully qualified (just like when writing a calculated column). The MAXX function (as well as FILTER, MINX, SUMX, etc.) are iterator functions that iterate over a table going row by row doing calculations within the row context of that table.

 

The table VALUES(ProductTypes[Type]) is a single column table with column name [Type] that consists of the just the product types that are selected by the slicer. Since I'm using MAXX on this table, I can reference just by column name. That wouldn't work with MAX, which requires an explicit column reference.

So the specification "VALUES('Product Types'[Type]" in the outer MAXX specifies the context for the inner MAXX as well?

Yes. It's a nested context. In the inner MAXX, you can access both [Product] and [Type]. Within the outer MAXX, but not in the inner MAXX, you can only access [Type].

Okay, I think I'm starting to get it. As an aside, how would you calculate the number of rows that meet the requirements the statement you provide is testing for? I need to give a count (I just found out)...

For that, you can just reuse the [Visible] measure.

 

If the Products table doesn't have any duplicate products in it, then you can simply write

 

Visible Count = SUMX(Products, [Visible])

Otherwise, you probably want

 

Visible Count = SUMX(DISTINCT(Products[Product]), [Visible])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.