Search in column with many to many relationship

[tameemyousaf]

Hi,

I have two table with name Actual & FlowTypeControl. I need to add a new column in Actual table based on text search (it will be trigger column from FlowTypeControl table) from comments column. 

Condition for new column will be like this:

IF Comments like FlowTypeControl.Trigger Then FlowTypeControl.FlowType ELSE Blank

Below are the screenshots of tables with sample data and required results.

Can anyone help?

[v-shex-msft]

Hi @tameemyousaf,

Did AlexisOlson 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements to find it more quickly.

If these also not help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[AlexisOlson · Accepted solution]

The CONTAINSSTRING function is useful here.

ActualFlowType =
VAR CRK = Actual[CountryRegionKey]
VAR Comments = Actual[Comments]
RETURN
    MAXX (
        FILTER (
            FlowTypeControl,
            FlowTypeControl[CountryRegionKey] = CRK
                && CONTAINSSTRING ( Comments, FlowTypeControl[Trigger] )
        ),
        FlowTypeControl[FlowType]
    )

[tameemyousaf]

Thanks @AlexisOlson

One more question, is there any way if we give just one region in FlowTypeControl table and set country blank and it will give data of all countries in that region. For example, if i give just North America and blank country then it should show the data for all the countries in North America. All countries of North America will be present in Actual table

[AlexisOlson]

I'm not sure where you "set country" or where "it will give data". Are you talking about slicer and visuals or measure variables and calculated tables or what?

[tameemyousaf]

@AlexisOlson I am not talking about any measure or table. I am talking about the data. When we add data in flowTypeControl table like this. Then how can we handle the situation of all countries.

[AlexisOlson]

How about matching on Region and Country separately rather than using the combined key and replacing blank with All?

ActualFlowType =
VAR Region = Actual[Region]
VAR Country = IF ( ISBLANK ( Actual[Country] ), "All", Actual[Country] )
VAR Comments = Actual[Comments]
RETURN
    MAXX (
        FILTER (
            FlowTypeControl,
            FlowTypeControl[Region] = Region
                && FlowTypeControl[Country] = Country
                && CONTAINSSTRING ( Comments, FlowTypeControl[Trigger] )
        ),
        FlowTypeControl[FlowType]
    )