Search if text is in range from-to (include "*" )

[Danielecc]

First of all...sorry for my bad english.

I want to know how can I search something like this, I have the "tabla 1" and the "tabla 2", searching values greater than or equal to "from" or Less than or equal to "To"...but the string could include "*" from the tablea 2, for example with:

I know is a very difficult excercise and i was looking for something like this in a lot of post, but nothing was similar.

[Anonymous]

Hi @Danielecc 

According to your screenshot, I am confused about your logic. I know you want to compare Tabla1[From] and Tabla1[To] with Tabla2[Code]. And return Tabla2[Code] which is >= Tabla1[From] or <= Tabla1[To] . Here I am confused about this logic and your result.

Ex: Caso1 From = AB05, To = SM20N. 

By your logic,  Tabla2[Code]>= AB05 or Tabla2[Code]<=SM20N.  The results in your screenshoot return to AB05,AB06,BA01,SM19. OK, AB05>=  AB05, AB06 >=AB05, BA01>= AB05 or SM19<=SM20N. 

Is LA24 >= AB05? I think LA24 are in the same format as BA01, LA24 should >BA01 >AB05.

I think you may lose some logic or filters, otherwise, by your logic, all codes in Tabla2 are the answers in Caso1.

Please show me more details about your calculate logic.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Danielecc]

Hi @Anonymous ,

You are right, I have a mistake on Caso1 example. I need the same that you say...because ALMOST all Code from table 2 are between AB05 AND SM20N (Except AB03, AB04 and SM22).

thank's for see the mistake and best regards.

[Anonymous · Accepted solution]

Hi @Danielecc 

In my test I find the difficult is from 'Tabla 1'[To]. From my test, I know "*" will be the smallest value, but in [To] it should be the biggest value. And [To] contains blank. So based on your logic and result, I will create a [New To] column to replace "*" by "ZZZ" and replace blank with [From]. Then create a range column to return 1 if the code in Tabla 2 is in range, if not it will return 0. Finally, I will summarize and filter the calcualted table to remove other columns.

Try this code.

Tabla 3 = 
VAR _JoinedTable =
    CROSSJOIN ( 'Tabla 1', 'Tabla 2' )
VAR _ADDNEWTO =
    ADDCOLUMNS (
        _JoinedTable,
        "New To",
            SWITCH (
                TRUE (),
                CONTAINSSTRING ( [To], "*" ), SUBSTITUTE ( [To], "*", "ZZZ" ),
                [To] = BLANK (), [From],
                [To]
            )
    )
VAR _ADDRANGE =
    ADDCOLUMNS (
        _ADDNEWTO,
        "In Range or not",
            IF ( [Code] >= [From] && [Code] <= [New To], 1, 0 )
    )
VAR _RESULT =
    SUMMARIZE ( FILTER ( _ADDRANGE, [In Range or not] = 1 ), [Nombre], [Code] )
RETURN
    _RESULT

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Danielecc]

Woooow @Anonymous I saw your .pbix file and your solution is ok, Now I have to try your code in the real PBIX file because the table1 has like 2 millions of rows and table2 has like 150 rows, I hope works there, but you resolve the problem.

Thank's a lot and best regards.

[Anonymous]

Hi Danielecc

1.In the Power BI desktop, go to Modeling tab and select New Table.

2. In the Formula Editor, do cross join of the above two tables.

           Joined Table = CROSSJOIN('Text_Table 1','Text_Table 2')

3. Create a calcualated column :

           In Range? = IF('Joined Table'[Code] >='Joined Table'[From] && 'Joined Table'[Code] <= 'Joined Table'[To], 1,0)

4. Apply a filter on "In Range?" =1

Thanks

Raj