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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Danielecc
Helper II
Helper II

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

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:

 

Imagen Tablas.PNG

 

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

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

1.png

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.

 

View solution in original post

5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

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.

Hi @v-rzhou-msft ,

 

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.

 

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.

1.png

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.

 

Woooow @v-rzhou-msft 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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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