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 RequetsText Field: ProgramNumerical Field : ID (a count of this not sum) orCOUNT : CALCULATE(COUNTA('External Requets'[ID]),NOT( ISBLANK('External Requets'[ID]))) Summarised data ProgramCountAll LBM29All LBM;#Other4CCC Support11CCC Support;#MMMcare;#All LBM3CCC Support;#MMMcare;#All LBM;#Other2CCC Support;#MMMcare;#Other1DDD, SSSS and CCC3DDD, SSSS and CCC;#EEEEE Services1EEEG Information Line23EEEG Information Line;#All LBM1EEEEE Services7FFF and Mummmm10FFF and Mummmm;#Other1FFF and Mummmm;#YYY and STUUU;#EEEEE Services1MMMcare80MMMcare;#All LBM;#Other1MMMcare;#Other7MMMcare;#PGTU Integrity;#All LBM1OOO ZZZs15OOO ZZZs;#DDD, SSSS and CCC;#Other1OOO ZZZs;#FFF and Mummmm;#DDD, SSSS and CCC;#YYY and STUUU;#EEEEE Services10OOO ZZZs;#FFF and Mummmm;#DDD, SSSS and CCC;#YYY and STUUU;#Other1OOO ZZZs;#Other4Other106PGTU Integrity4YYY and STUUU2YYY and STUUU;#EEEEE Services2OOO ZZZs;#DDD, SSSS and CCC;#EEEEE Services2OOO ZZZs;#FFF and Mummmm;#DDD, SSSS and CCC;#YYY and STUUU;#EEEEE Services;#Other1CCC Support;#MMMcare;#OOO ZZZs;#FFF and Mummmm;#DDD, SSSS and CCC;#YYY and STUUU;#EEEEE Services;#Other1YYY and STUUU;#EEEEE Services;#Other1FFF and Mummmm;#EEEEE Services1FFF and Mummmm;#YYY and STUUU1PGTU Integrity;#All LBM1OOO ZZZs;#YYY and STUUU1 Desired result ProgramIDCCC Support18MMMcare96PGTU Integrity6OOO ZZZs36FFF and Mummmm27DDD, SSSS and CCC20YYY and STUUU21EEEEE Services27All LBM42Other131EEEG Information Line24

Anonymous , 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/MKKWeOqFG4chttps://medium.com/@amitchandak/power-bi-search-text-parts-in-text-ad2f9783ffa2

Anonymous

Not applicable

2 years ago

Solved

help with text search with a deliminter

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)

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

Need Help

Ashish_Mathur
2 years ago
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
2 years ago
Anonymous , 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

Anonymous

2 years ago

Hi Anonymous ,
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.

help with text search with a deliminter.pbix43 KB

3 Replies

amitchandak
Super User
2 years ago
Anonymous , 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

Anonymous

Not applicable

2 years ago

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.

help with text search with a deliminter.pbix43 KB

Ashish_Mathur
Super User
2 years ago
Hi,

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

Measure = sum(Data[Count])

Hope this helps.

Forum Discussion

help with text search with a deliminter

3 Replies

Related content

Slicer Search Bar

Slicer search recommendation

Iphone 16 pro searching

How to search specific word in table filter Calculations

Search disabled in Paginated Report