help with text search with a deliminter

[Haydn_R]

I tring to achieve the below, look for a value within a string and count the ID if the condition is met.

The 1st table is a summarised view of the data and the 2nd table is the desired outcome.

I've tried the below, but it is returning 106 for "Other", when it should be 131.

Program External Count = 
SWITCH(
    TRUE(),   
    CONTAINSSTRING('External Requets'[Program],"Other"),'External Requets'[COUNT],

    BLANK()
)

Data set: External Requets
Text Field: Program

Numerical Field : ID (a count of this not sum) 

or

COUNT : CALCULATE(COUNTA('External Requets'[ID]),NOT( ISBLANK('External Requets'[ID]))) 

Summarised data

Program	Count
All LBM	29
All LBM;#Other	4
CCC Support	11
CCC Support;#MMMcare;#All LBM	3
CCC Support;#MMMcare;#All LBM;#Other	2
CCC Support;#MMMcare;#Other	1
DDD, SSSS and CCC	3
DDD, SSSS and CCC;#EEEEE Services	1
EEEG Information Line	23
EEEG Information Line;#All LBM	1
EEEEE Services	7
FFF and Mummmm	10
FFF and Mummmm;#Other	1
FFF and Mummmm;#YYY and STUUU;#EEEEE Services	1
MMMcare	80
MMMcare;#All LBM;#Other	1
MMMcare;#Other	7
MMMcare;#PGTU Integrity;#All LBM	1
OOO ZZZs	15
OOO ZZZs;#DDD, SSSS and CCC;#Other	1
OOO ZZZs;#FFF and Mummmm;#DDD, SSSS and CCC;#YYY and STUUU;#EEEEE Services	10
OOO ZZZs;#FFF and Mummmm;#DDD, SSSS and CCC;#YYY and STUUU;#Other	1
OOO ZZZs;#Other	4
Other	106
PGTU Integrity	4
YYY and STUUU	2
YYY and STUUU;#EEEEE Services	2
OOO ZZZs;#DDD, SSSS and CCC;#EEEEE Services	2
OOO ZZZs;#FFF and Mummmm;#DDD, SSSS and CCC;#YYY and STUUU;#EEEEE Services;#Other	1
CCC Support;#MMMcare;#OOO ZZZs;#FFF and Mummmm;#DDD, SSSS and CCC;#YYY and STUUU;#EEEEE Services;#Other	1
YYY and STUUU;#EEEEE Services;#Other	1
FFF and Mummmm;#EEEEE Services	1
FFF and Mummmm;#YYY and STUUU	1
PGTU Integrity;#All LBM	1
OOO ZZZs;#YYY and STUUU	1

Desired result

Program	ID
CCC Support	18
MMMcare	96
PGTU Integrity	6
OOO ZZZs	36
FFF and Mummmm	27
DDD, SSSS and CCC	20
YYY and STUUU	21
EEEEE Services	27
All LBM	42
Other	131
EEEG Information Line	24

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, split the contents into rows using ; as the delimiter.  Write this measure

Measure = sum(Data[Count])

Hope this helps.

[amitchandak · Accepted solution]

@Haydn_R , You can try the suggestion from @Ashish_Mathur , If that does not workout, Please check this

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
https://medium.com/@amitchandak/power-bi-search-text-parts-in-text-ad2f9783ffa2

[v-yifanw-msft · Accepted solution]

Hi @Haydn_R ,
The helpful suggestions from @Ashish_Mathur  and @amitchandak can provide you with many ideas. I have created an example for you to solve the problem.
1. Separate Program with ;# in Power Query

2.Unpivot Column from Program.1 to Program.8

Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVNdT8IwFP0rzfq6h22oaPqEmyMmNDMZe9gIDwtWXQIbqcXEf29XpaOlpRLCfeu5536ck97Fwpus12D2iD3fix68pS8BBDP2QSjHbwQcxzHId9ttRxnHwlAHEcQYr2pKEBxajtysYU50grzn/I5NksQHOQ9Qt6+AV8hRRxkEn/oAOaFfzYp8yh4cnILn9q2jm5o1XQtmTUv6LUb29KE02UVtPhZwmqZiAbzb8OjZgQHXZenZsizFO58XRWEV8ucRf98Hh4DB4VDN7+GxCr9M5wWXzsg7bdj3seYsy0BVVWKDWwVB0OC/Onyg6moNpU4Dgot72rZTP79kBXfirVokaUpv+aNdKiK3h86aS83UjbBc4dXmnUXWh1sO4+Q5SdZ/f7uxfvkD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Program = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Program", type text}, {"Count", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Program", Splitter.SplitTextByDelimiter(";#", QuoteStyle.Csv), {"Program.1", "Program.2", "Program.3", "Program.4", "Program.5", "Program.6", "Program.7", "Program.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Program.1", type text}, {"Program.2", type text}, {"Program.3", type text}, {"Program.4", type text}, {"Program.5", type text}, {"Program.6", type text}, {"Program.7", type text}, {"Program.8", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Count"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Value", "Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "Program"}})
in
    #"Renamed Columns"

Fianl output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[amitchandak · Accepted solution]

@Haydn_R , You can try the suggestion from @Ashish_Mathur , If that does not workout, Please check this

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
https://medium.com/@amitchandak/power-bi-search-text-parts-in-text-ad2f9783ffa2

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, split the contents into rows using ; as the delimiter.  Write this measure

Measure = sum(Data[Count])

Hope this helps.