Looping in DAX, Query?

[Roboboboberts]

Hi,
I am struggling to find a DAX or M code solution to my situation. I can only replicate this using VBA, which I do not want to do. In short, I want to know how to loop through tables using m code or Dax. Is it even possible?

The case:

• I have two tables.
• Table one contains one column that contains random text values.
• Table two is the table shown in the below image.
  

For example, I have a value in table ones cell "AmznPrimeDE*123456DE". I want to have a function that will search every string from table twos column “search” and if true, return me a value from column “Output.”

[Melissa]

Hi @Roboboboberts,

Sounds interesting, you should probably provide a representative sample of what you are dealing with and a mock up of the expected results, if you require community help in designing a solution…

[Roboboboberts]

Thank you for the advice, I will do it in future.

The solution I want to get. Basically, I want to replicate Excel`s function SEARCH with looping.

• Search every string from Table 2 column Search in Table 1 column ID.
• Those elements in table 2 will always change, it won`t be country code or anything.

[Melissa]

Hi @Roboboboberts,

How representative is the depicted example? Because it looks as though you only have to:

1. extract the last 2 characters from the entire string OR
2. the last 2 characters before the “*”

especially in the first case you can easily achieve that using the UI. Select the ID column from Table 1, go to the Add Column ribbon and select “Last Characters”

Next go to the Home ribbon and choose Merge select the new column from Table 1 and the Search column from Table 2, then extract the Output column.

I hope this is helpful.
Should you need further assistance, provide a sample in XLSX - thanks

[BrianJ]

@Roboboboberts,

As usual, @Melissa has provided a great solution above. I wanted to play with a few aspects of it unrelated to this question, so I created the following two files based on your example.

The first extracts the last two characters of the ID string, while the second extracts the two characters immediately preceding the * in ID.

Just thought you might find them useful, so I wanted to pass them along to you.

– Brian

eDNA Looping in DAX Query.pbix (19.0 KB)
eDNA Looping in DAX Query2.pbix (19.8 KB)

[Roboboboberts]

Thank you both for the input.
The thing is that the string I search for will not always be located before * and be two strings long. For example, the ID can be AmnCarrotbreadPrime and word bread would be the word I search for. I really think I need to loop through table 2 using DAX or M code if possible. Otherwise, I am planning to write a VBA to prepare the data before importing it in Power Query.

[Melissa]

Hi @Roboboboberts,

This forum is full of volunteers that give up their own time to help others, all we ask in return is that if you post a question you also provide as much detail and mock up of what you are trying to achieve.

I remain confident that this can be solved in M as well but without a good sample and clear description of the requirement we will be going around in circles.

Question for you is or should the search string be case sensitive?

[BrianJ]

@Roboboboberts,

Thanks for the clarification. I have a pretty clear idea how this could be done in DAX using the CONTAINSSTRING function. I’m sure @Melissa already has a Power Query solution in mind as well.

If you could please prepare a PBIX with some representative sample data that we can use to test solutions, I would be glad to work on this with you.

Thanks.

• Brian

[Roboboboberts]

Hi @Melissa and @BrianJ,
I have attached the sample file to this reply. I am slowing making the programme for myself, the actual list can be thousands of lines. For the example, there are five lines.

THank you!!!

Help!.pbix (25.7 KB)

[Rajesh]

Hi @Roboboboberts

I think this is what you are expecting.

Help!.pbix (27.1 KB)

[BrianJ]

@Roboboboberts,

Nice solution by @Rajesh. I think Power Query is really the way to go here from a performance standpoint, but if you’re curious about a DAX solution, here’s one approach:

1. create the following crossjoined table:

    CJ Table = 
   
    CROSSJOIN(
        DISTINCT( MainTable[Text] ),
        DIM_key
    )
   

2. add the following calculated column to it:

Test Contain =

IF( CONTAINSSTRING(
    'CJ Table'[Text],
    'CJ Table'[DimText]
    ),
    'CJ Table'[Translation],
    BLANK()
)

3. use this final measure to return the desired value:

   Translate Lookup =

    VAR vTable =
    FILTER(
        'CJ Table',
        'CJ Table'[Test Contain] <> BLANK()
    )
   
    VAR Result =
    CALCULATE(
        SELECTEDVALUE( 'CJ Table'[Test Contain] ),
        FILTER(
            vTable,
            'CJ Table'[Text] = SELECTEDVALUE( MainTable[Text] )
        )
    )
   
    RETURN
    Result
   

Here it is all put together:

I hope this is helpful. Full solution file attached below.

• Brian
  eDNA Forum - CONTAINSSTRING Test Lookup Solution.pbix (32.3 KB)

[Rajesh]

Thanks @BrianJ

@Roboboboberts Here is another simple solution using DAX.

This is 3rd Solution to your requirement.

Create Calculated Column.

Help!.pbix (27.9 KB)

[Melissa]

@Rajesh, I like your style
also loved that you stayed in “table space” - excellent solution, if you ask me !!

Here’s my version for those of you who are interested .

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JctBC4IwGIDhv/LhMWIgQXRdoWmuKaV06PMwddFgfgNxSf76Qm/P4X2fz4D3MxWD6bUUG8TdAfhHk9dwcW+CGDJNpLsv4jRNTPVqdsTIIgb19v/6zjRWQ5UhhvDg9ySV5zKXUPBbBuG+TCAWiKprLGvdshyVHU0LJ+epNRZeboCURj2QGo0jZSHqfLt4zbkor1yuzkXJoiqo6x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    SearchTable = Table.Buffer( DIM_key ),
    GetResult = Table.AddColumn(Source, "Result", each 
        let 
            SearchString = [Text],
            GetRows = Table.SelectRows( SearchTable, each Text.Contains( Text.Lower( SearchString ), Text.Lower( [Text] ))=true ),
            GetValue = GetRows{0}[Translation]?
        in
            GetValue, type text)
in
    GetResult

.
@Roboboboberts the outcome is the same as Rajesh’s query of course.

I hope this is helpful.

[Rajesh]

Thanks @Melissa

[BrianJ]

@Rajesh,

Nice! Very elegant, efficient solution. .

• Brian

[Roboboboberts]

Thank you all! Really helpful, I cannot figure out a way to give you all the “Solution”.
Crazy, I have written each so many times but never really understood how to use it.

Thank you all again! This problem is now solved!!!
@Melissa @Rajesh @BrianJ

[BrianJ]

@Roboboboberts,

Glad you got what you needed. As you can tell, this one seemed to have captured everyone’s imagination.

@Rajesh gets the solve on this one. He was first with the Power Query solution, and absurdly more efficient on the DAX solution.

• Brian

[BrianJ]

@Rajesh,

FYI – I liked your solution so much that I posted it (with attribution, of course) to the Analyst Hub Community section, so that others could easily copy it into their own DAX libraries.

Dimension Lookup Based on Internal String Matching - Analyst Hub by...

• Brian

[Rajesh]

@BrianJ

I’m glad to hear that you liked it.

[BrianJ]

@Rajesh,

As I was working on my solution, I kept thinking "there’s got to be an easier way to do this ". Turns out I was right…

This is exactly the type of dynamic I hope develops in the Problem of the Week discussions, where we can explore different approaches to the same problem, and come away with some innovative solutions that the entire community can apply to similar scenarios.

• Brian