cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DIACHROMA
Helper II
Helper II

Filter a table based on concatenated values

Hi guys, 

 

I am working with a table in which the products are concatenated in the same column instead of having one row per product row :

 

REFPRODUCT OFFERED
001AAA BBB DDD
002CCC BBB
003AAA
004CCC DDD
005BBB AAA

 

To give you a bit of context, my fact table is a list of sales visits where sales reps can offer multiple products. When the salesperson enters their report, they can check off as many products as they want. All these products arrive in one and the same column.

 

Besides, I have a Product dim table : 

 

ProductName
AAA
BBB
CCC
DDD

 

I cannot create a relationship between the "Product" and "Offered Product" columns and I do not have access to the data (which is managed by the IT).

 

However, I absolutely need to know the number of times this or that product has been offered during the visits.

 

So I created the below DAX measure : 

 

FoundProduct =

VAR SearchValue = SEARCH( SELECTEDVALUE( ProductName ) ; SELECTEDVALUE( ProductOffered ) ; ; BLANK() )

 

RETURN

IF( SearchValue > 0 ; "Found" )

 

And then, when adding the "FoundProduct" measure in the filter section of a visual i was hoping it works.

However, I have several problems: sometimes the results are completely incorrect and above all, when I select several products, I have completely inconsistent results.

 

How can I use my product table in a slicer to filter my visits table, while keeping the possibility of selecting several products?

 

I can't wait to read your feedback!

A big thank you in advance,
Pauline

 

1 ACCEPTED SOLUTION

@DIACHROMA OK, try this as the RETURN then:

COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__AllRefsTable,[__Ref]<>BLANK()),"__Ref",[__Ref])))

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

20 REPLIES 20
Greg_Deckler
Super User
Super User

@DIACHROMA Well, I would recommend that you split that column in Power Query using space as delimiter, select your REF column and unpivot other columns. That would likely make this tremendously easier. If for some reason you cannot do that try Count of List items: Count of List Items - Microsoft Power BI Community

In your case: 

Measure =
  VAR __String = MAX('dimProduct'[ProductName])
  VAR __Strings = CONCATENATEX('Table',[PRODUCT OFFERED],", ")
  VAR __Length = LEN(__String)
RETURN
  ( LEN(__Strings) - LEN(SUBSTITUTE(__Strings,__String,"")) ) / __Length

If you put your ProductName in a table along with this measure, should work.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Hi @Greg_Deckler , 

 

Thank you very much for your reply 🙂

 

I created this formula, I have a result but it does not work when I select several products. At this point the data becomes inconsistent. Maybe it's because the products are not concatenated in the same order each time? Or maybe it comes from my model, I don't know ...

 

And when I don't select any product, the result shows 0 (and not the total as usual).

 

While waiting to find a solution I added the visual "Text Filter" from the market place. It works when I type in the name of a product, the numbers look ok but unfortunately I cannot enter multiple products in the search bar.

 

So I am still working on finding a better solution...

 

Pauline

@DIACHROMA OK, let's say you just want a total number of times things have been sold with only a slicer and let's say a Card visual to display the result. Try this:

Measure =
  VAR __ProductsToSearchFor = SELECTCOLUMNS('dimProduct',"__ProductName",[ProductName])
  // this gets the products selected in the slicer in a table with just ProductName column
  VAR __Table =
    ADDCOLUMNS(
      __ProductsToSearchFor,
      "__Total",
        VAR __String = [ProductName]
        VAR __Strings = CONCATENATEX('ProductsOfferedTable',[PRODUCT OFFERED],", ")
        VAR __Length = LEN(__String)
      RETURN
        ( LEN(__Strings) - LEN(SUBSTITUTE(__Strings,__String,"")) ) / __Length
    )
RETURN
  SUMX(__Table,[__Total])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Thanks again @Greg_Deckler !

 

So I tested this formula in a card. When I select a single product, I have the right result. But when I select more than one product, some visits are counted twice.

Example :

 

REFOFERRED PRODUCTS
001AAA
002AAA BBB
003BBB

 

If I select AAA and BBB in the silcer, the result will be 4 (instead of 3 which is the expected result because there are 3 commercial visits ). 

 

Also, if no product is selected in my slicer, the total is incorrect. It is much too high: 6599 instead of 1445. Again, if I select all products in the slicer the result is still to high : 1557 instead of 1445 visits in total. 

@DIACHROMA OK, let's go this route then:

 

Measure =
  VAR __ProductsToSearchFor = SELECTCOLUMNS('dimProduct',"__ProductName",[ProductName])
  // this gets the products selected in the slicer in a table with just ProductName column
  VAR __Table =
    ADDCOLUMNS(
      __ProductsToSearchFor,
      "__Refs",
        VAR __String = [ProductName]
        VAR __Refs = 
          CONCATENATEX(
            SELECTCOLUMNS(
              FILTER('ProductsOfferedTable',CONTAINSSTRING([PRODUCT OFFERED],__String)),
              "__Ref",[REF]
            ),
            [__Ref],"|"
          )
      RETURN
        [__Refs]
    )
  VAR __AllRefs = CONCATENATEX(__Table,[__Refs],"|")
  VAR __Count = COUNTROWS(__AllRefs)
  VAR __AllRefsTable = 
    ADDCOLUMNS(
      GENERATESERIES(1,__Count,1),
      "__Ref",PATHITEM(__AllRefs,[Value],TEXT)
    )
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__AllRefsTable,"__Ref",[__Ref])))

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

I can't write :

  VAR __Count = COUNTROWS(__AllRefs)

It doesn't allow me to put "__AllRefs" in the COUNTROWS

 

@DIACHROMA My bad:

Measure =
  VAR __ProductsToSearchFor = SELECTCOLUMNS('dimProduct',"__ProductName",[ProductName])
  // this gets the products selected in the slicer in a table with just ProductName column
  VAR __Table =
    ADDCOLUMNS(
      __ProductsToSearchFor,
      "__Refs",
        VAR __String = [ProductName]
        VAR __Refs = 
          CONCATENATEX(
            SELECTCOLUMNS(
              FILTER('ProductsOfferedTable',CONTAINSSTRING([PRODUCT OFFERED],__String)),
              "__Ref",[REF]
            ),
            [__Ref],"|"
          )
      RETURN
        [__Refs]
    )
  VAR __AllRefs = CONCATENATEX(__Table,[__Refs],"|")
  VAR __Count = __Len - LEN(SUBSTITUTE(__AllRefs,"|","")) + 1
  VAR __AllRefsTable = 
    ADDCOLUMNS(
      GENERATESERIES(1,__Count,1),
      "__Ref",PATHITEM(__AllRefs,[Value],TEXT)
    )
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__AllRefsTable,"__Ref",[__Ref])))

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Sorry me again... In the VAR __Count I can't put __Len as it doesnt exist earlier as a var

@DIACHROMA I'll get this right eventually:

Measure =
  VAR __ProductsToSearchFor = SELECTCOLUMNS('dimProduct',"__ProductName",[ProductName])
  // this gets the products selected in the slicer in a table with just ProductName column
  VAR __Table =
    ADDCOLUMNS(
      __ProductsToSearchFor,
      "__Refs",
        VAR __String = [ProductName]
        VAR __Refs = 
          CONCATENATEX(
            SELECTCOLUMNS(
              FILTER('ProductsOfferedTable',CONTAINSSTRING([PRODUCT OFFERED],__String)),
              "__Ref",[REF]
            ),
            [__Ref],"|"
          )
      RETURN
        [__Refs]
    )
  VAR __AllRefs = CONCATENATEX(__Table,[__Refs],"|")
  VAR __Count = LEN(__AllRefs) - LEN(SUBSTITUTE(__AllRefs,"|","")) + 1
  VAR __AllRefsTable = 
    ADDCOLUMNS(
      GENERATESERIES(1,__Count,1),
      "__Ref",PATHITEM(__AllRefs,[Value],TEXT)
    )
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__AllRefsTable,"__Ref",[__Ref])))

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Greg_Deckler , 

 

Ok now we soooo close !! 

 

I get the correct result for all product individually AND with multiple selections except for a product whose result must be 0, for this one I have 1.

 

And therefore the total shows me 1446 instead of 1445.

I double-double checked and it should be 1445 - I don't have any visit for the product where I get 1. 

 

I'm sorry I'm completely lost in your formula (way too complex for me) so I can't seem to figure out when the 1 can slip in instead of counting blank or 0.

 

Thank you in advance for your help 🙂

 

@DIACHROMA OK, so I know you said you double-checked but could you check with this measure to see if it returns 1446 or 1445.

 

Measure = COUNTROWS(DISTINCT(SELECTCOLUMNS('ProdutsOfferedTable',"__Ref",[REF])))

 

 

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

It returns 1445

@DIACHROMA OK, well that is odd then. What if you create a new table like this and we can check what is being returned, maybe there is a blank row being returned and we can just filter it out. It's the exact same formula, you just return the __AllRefsTable instead.

 

New Table =
  VAR __ProductsToSearchFor = SELECTCOLUMNS('dimProduct',"__ProductName",[ProductName])
  // this gets the products selected in the slicer in a table with just ProductName column
  VAR __Table =
    ADDCOLUMNS(
      __ProductsToSearchFor,
      "__Refs",
        VAR __String = [ProductName]
        VAR __Refs = 
          CONCATENATEX(
            SELECTCOLUMNS(
              FILTER('ProductsOfferedTable',CONTAINSSTRING([PRODUCT OFFERED],__String)),
              "__Ref",[REF]
            ),
            [__Ref],"|"
          )
      RETURN
        [__Refs]
    )
  VAR __AllRefs = CONCATENATEX(__Table,[__Refs],"|")
  VAR __Count = LEN(__AllRefs) - LEN(SUBSTITUTE(__AllRefs,"|","")) + 1
  VAR __AllRefsTable = 
    ADDCOLUMNS(
      GENERATESERIES(1,__Count,1),
      "__Ref",PATHITEM(__AllRefs,[Value],TEXT)
    )
RETURN
  __AllRefsTable

 

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

It returns this error : 

DIACHROMA_0-1631906235837.png

 

@DIACHROMA Well that makes no sense since the length of the text should be the same for the measure! Let's try another route, create this the same measure that returns 1446 but with this in the last RETURN line and maybe put it in a Card visual or something:

CONCATENATEX(DISTINCT(SELECTCOLUMNS(__AllRefsTable,"__Ref",[__Ref])),[__Ref],",")

What we are looking for is ",," for example or some other value that doesn't look quite right.

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Ok ! I did it, it returns Visit IDs in one value. I export the result to excel to check it, I have 1445 IDs in total. 

 

I don't know if it can help, but the 1 which is extra in the total appears when I select a product which should normally show me Blank or 0. Isn't there something in the formula that could make 1 to this product that should not appear?

@DIACHROMA OK, try this as the RETURN then:

COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__AllRefsTable,[__Ref]<>BLANK()),"__Ref",[__Ref])))

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

It works !!!!!! 😊

A big thank you @Greg_Deckler , you can't imagine how much you helped me today (tonight for me)!

Thank you again and have a great weekend !!!

 

Pauline 

@DIACHROMA The formula is intended to work in conjunction with the ProductName from the Products table being in a table visualization along with the measure. You could use a slicer to limit which products are in the table visual. I will think about how this might be done without that, pretty sure I could create a solution around that.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Ah yes indeed! When I filter my slicer products report the total is lower but still incorrect: 1557 (instead of 1445) probably because some visits are counted multiple times.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors