Exclude multiple texts prefix from the Text Field

Hello Team,

We are having field Deliverable Doc ID, which is a text week trying to exclude Text which stated from ZOQT-1234 and N/A.

I have tried multiple ways using this formula is excluding the text from the filter, but still I am able to see the issues with the same values, please let me know if I am doing anything wrong

I want to exclude the text which starts with ZOQT & N/ in the below report

Aggregate(
Filter(
[Deliverable Doc ID].Members,
[Deliverable Doc ID].CurrentHierarchyMember.Name
NOT MATCHES “.ZOQU.|.N/.
)
)

Report Definition
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Business Steps Testing Indicator Overview”,
“folder_name”: “LDC - Build - Testing (OQT/PQT)”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Issues created]”],“members”:},{“name”:“R2.0 Testing Indicator”,“selected_set”:[“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”],“members”:[{“depth”:0,“name”:“All R2.0 Testing Indicators”,“full_name”:“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:1,“name”:“(none)”,“full_name”:“[R2.0 Testing Indicator].[(none)]”,“removed”:true,“parent_full_name”:“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”},{“depth”:1,“name”:“Out of Scope”,“full_name”:“[R2.0 Testing Indicator].[Out of Scope]”,“removed”:true,“parent_full_name”:“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”},{“depth”:1,“name”:“Whitelist (No Retest Required)”,“full_name”:“[R2.0 Testing Indicator].[Whitelist (No Retest Required)]”,“removed”:true,“parent_full_name”:“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”}],“bookmarked_members”:}]},“rows”:{“dimensions”:[{“name”:“Workstream”,“selected_set”:[“[Workstream].[LDC - Workstreams]”],“members”:[{“depth”:0,“name”:“LDC - Workstreams”,“full_name”:“[Workstream].[LDC - Workstreams]”,“calculated”:true,“drillable”:true,“drilled_into”:false,“removed”:false,“expanded”:true}],“bookmarked_members”:}]},“pages”:{“dimensions”:[{“name”:“Time”,“selected_set”:[“[Time].[Created After Mar 1st, 2024]”],“members”:[{“depth”:0,“dimension”:“Time”,“name”:“Created After Mar 1st, 2024”,“formula”:“Aggregate(\n[Time].[Month].DateMembersBetween(‘Mar 01 2024’, ‘today’)\n)\n”,“format_string”:“”,“full_name”:“[Time].[Created After Mar 1st, 2024]”,“drillable”:true,“annotations”:null,“calculated”:true}],“bookmarked_members”:,“current_page_members”:[“[Time].[Created After Mar 1st, 2024]”]},{“name”:“Issue Type”,“selected_set”:[“[Issue Type].[All Issue Types]”],“members”:[{“depth”:0,“name”:“All Issue Types”,“full_name”:“[Issue Type].[All Issue Types]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Business Step”,“full_name”:“[Issue Type].[Business Step]”,“parent_full_name”:“[Issue Type].[All Issue Types]”}],“bookmarked_members”:,“current_page_members”:[“[Issue Type].[Business Step]”]},{“name”:“Workstream”,“duplicate”:true,“selected_set”:[“[Workstream].[LDC - Workstreams]”],“members”:[{“depth”:0,“name”:“LDC - Workstreams”,“full_name”:“[Workstream].[LDC - Workstreams]”,“calculated”:true,“drillable”:true,“expanded”:false,“drilled_into”:false,“removed”:false}],“bookmarked_members”:,“current_page_members”:[“[Workstream].[LDC - Workstreams]”]},{“name”:“Testing Event”,“selected_set”:[“[Testing Event].[All Testing Events]”],“members”:[{“depth”:0,“name”:“All Testing Events”,“full_name”:“[Testing Event].[All Testing Events]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“(none)”,“full_name”:“[Testing Event].[(none)]”,“parent_full_name”:“[Testing Event].[All Testing Events]”}],“bookmarked_members”:,“current_page_members”:[“[Testing Event].[(none)]”]},{“name”:“Status”,“selected_set”:[“[Status].[All but cancelled / rejected]”],“members”:[{“depth”:0,“name”:“All but cancelled / rejected”,“full_name”:“[Status].[All but cancelled / rejected]”,“calculated”:true,“drillable”:true,“dimension”:“Status”,“formula”:“Aggregate(\n Except(\n [Status].[Status].Members,\n { \n [Status].[Cancelled],\n [Status].[Rejected]\n }\n )\n)”,“format_string”:“”,“annotations”:null,“dependent_calculated_members_count”:0,“dependent_report_ids”:[1197164,1210017,2528683,2528616,2638441,1574859,1606503,1768131,1793543,1768128,1768127,1767920,1561556,1591978,1577002,1664941,1665062,2128714,1685220,1896792,1471977,1626072,1015680,1198716,2393814,2393876,1250648,2147693,2674532,2414551,2103377,2095599,1103908,2426388,2083775,2146463,2119569,2426390,2095563,2159868,2144867,2482532,2482522,2159911,2137141,2099764,1634733,855622,1680780,1195755,2099526,956066,898558,1177766,1183959,2526720,2526282,1080536,1086600,1086598,1086601,1086599,1086602,1086603,1241893,1241896,1236900,1086695,1090193,1086671,1090198,1090196,1236892,1090205,1090204,1175516,1090194,1090191,966458,955255,1605272,957900,2393911,1597400,1704881,2393897,2393812,1173001,1634634,1597348,2641880,1301213,1305727,1181889,2430069,2430085,1688567,1912289,2073293,1903747,1709420,1912311,1688570,1709404,1912305,2377919,2508502,1688569,1709370,1912304,1893145,1683525,1709424,1709407,1912323,2146476,2139821,1709405,1912307,2159873,2139819,2144895,1688571,2159939,2139820,1080490,1634710,2289426,1283178,2159833,1283177,1634708,2137226,1606504,1242124,1242182,1894700,2368156,1894684,2528790,2358918,1374353,1519268,1471592,1709275,1314906,1314863,1314883,1314907,1314882,2146493,2137228,1495920,1495919,1640599,1640595,1634282,1634301,1608810,2105860,2412392,2480999,1636376,2518716,2159895,2163571,2518718,2145817,2139743,1253530,1187213,1213902,1215546,1236898,1086693,956062,1236888,1241892,1241895,1938815,2139714,2518726,2159944,2163572,2429594,2426347,2426514,2391771,2402906,2426423]}],“bookmarked_members”:,“current_page_members”:[“[Status].[All but cancelled / rejected]”]},{“name”:“Release_Version”,“selected_set”:[“[Release_Version].[All Release_Versions]”],“members”:[{“depth”:0,“name”:“All Release_Versions”,“full_name”:“[Release_Version].[All Release_Versions]”,“drillable”:true,“type”:“all”}],“bookmarked_members”:,“current_page_members”:[“[Release_Version].[All Release_Versions]”]},{“name”:“Deliverable Doc ID”,“selected_set”:[“[Deliverable Doc ID].[All but N/A]”,“[Deliverable Doc ID].[All But ZOQT]”,“[Deliverable Doc ID].[All Scripted/Unscripted Test]”,“[Deliverable Doc ID].[ALL tEST 2]”],“members”:[{“depth”:0,“dimension”:“Deliverable Doc ID”,“name”:“ALL tEST 2”,“formula”:“Aggregate({\n[Deliverable Doc ID].[All But ZOQT],\n[Deliverable Doc ID].[All but N/A]\n})”,“format_string”:“”,“full_name”:“[Deliverable Doc ID].[ALL tEST 2]”,“drillable”:true,“annotations”:null}],“bookmarked_members”:,“current_page_members”:[“[Deliverable Doc ID].[ALL tEST 2]”]}]},“options”:{“nonempty”:true,“total”:true},“view”:{“current”:“table”,“maximized”:false,“table”:{“row_dimension_headers”:{“Workstream”:true}}},“calculated_members”:}
} ],
“calculated_members”: [{“name”:“All but cancelled / rejected”,“dimension”:“Status”,“formula”:“Aggregate(\n Except(\n [Status].[Status].Members,\n { \n [Status].[Cancelled],\n [Status].[Rejected],\n [Status].[On Hold]\n }\n )\n)”,“format_string”:“”},{“name”:“Commercial”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Commercial],\n[Workstream].[COM-Customer 360],\n[Workstream].[COM-Evidence-to-Value],\n[Workstream].[COM-Lead-to-Quote],\n[Workstream].[COM-Order-to-Release],\n[Workstream].[COM-Quote-to-Contract],\n[Workstream].[COM-Release-to-Invoice],\n[Workstream].[COM-Strategy-to-Demand],\n[Workstream].[COMP-Pillar: Commercial],\n[Workstream].[DAT-Commercial]\n})”,“format_string”:“”},{“name”:“Finance”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Finance \u0026 Procurement],\n[Workstream].[DAT-Finance \u0026 Controlling],\n[Workstream].[DAT-Customer],\n[Workstream].[FIN-Asset Accounting and Projects],\n[Workstream].[FIN-FC\u0026C (incl. Analytics)],\n[Workstream].[FIN-General Ledger and Period End],\n[Workstream].[FIN-I2C - Invoice 2 Cash],\n[Workstream].[FIN-Operations Finance],\n[Workstream].[FIN-Intercompany],\n[Workstream].[FIN-Revenue Management],\n[Workstream].[FIN-RDF],\n[Workstream].[FIN-Tax],\n[Workstream].[FIN-Treasury and Cash-Management],\n[Workstream].[FINP-Pillar: Finance],\n[Workstream].[DAT-Customer \u0026 Commercial],\n[Workstream].[FIN-RDF and intercompany],\n[Workstream].[FIN-BPA]\n})”,“format_string”:“”},{“name”:“Procurement”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[DAT-Supplier \u0026 Procurement],\n[Workstream].[PUR-Accounts Payable and Supplier Experience (SLP)],\n[Workstream].[PUR-CE and Transactional Direct Procurement],\n[Workstream].[PUR-CE and Transactional Indirect Procurement and Front door],\n[Workstream].[PUR-Strategic Procurement and S2C],\n[Workstream].[PUR-S2P],\n[Workstream].[SEP-Supplier Exp - Supplier Information Mgmt],\n[Workstream].[PURP-Pillar: Procurement],\n[Workstream].[SEP-Supplier Exp- Supplier Data Quality]\n})”,“format_string”:“”},{“name”:“Operations”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Operations],\n[Workstream].[DAT-Material \u0026 Operations],\n[Workstream].[DAT-Product \u0026 Brand],\n[Workstream].[OPS-Manufacturing - Asset Management],\n[Workstream].[OPS-Manufacturing - Manufacturing],\n[Workstream].[OPS-SCM - Demand Planning],\n[Workstream].[OPS-SCM - External Manufacturing],\n[Workstream].[OPS-SCM - Intercompany Order Management / Customs and Trade Compliance],\n[Workstream].[OPS-SCM - Lifecycle Management and Implementation],\n[Workstream].[OPS-SCM - Logistics/Warehousing],\n[Workstream].[OPS-SCM - Long Term, Demand and Supply Planning],\n[Workstream].[OPS-SCM - Production planning],\n[Workstream].[OPS-SCM - Supply Planning],\n[Workstream].[OPS-TRD - Clinical Supply],\n[Workstream].[OPS-TRD - Embedded],\n[Workstream].[SCM Supply Chain Management],\n[Workstream].[OPS-Quality (incl. OBBR)],\n[Workstream].[OPS-SCM - Ariba SCC],\n[Workstream].[OPSP-Pillar: Manufacturing],\n[Workstream].[OPSP-Pillar: Quality],\n[Workstream].[OPSP-Pillar: Supply Chain],\n[Workstream].[MOM - Masters of Material],\n[Workstream].[OPSP-Pillar: TRD]\n})”,“format_string”:“”},{“name”:“LDC - Workstreams”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[Commercial],\n[Workstream].[Finance],\n[Workstream].[Procurement],\n[Workstream].[Operations],\n[Workstream].[Solutions],\n[Workstream].[DAT-Migration],\n[Workstream].[N/A],\n[Workstream].[Unmatch],\n[Workstream].[(none)]\n})”,“format_string”:“”},{“name”:“Solutions”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Integration],\n[Workstream].[DAT-Integration],\n[Workstream].[SOL-BPM],\n[Workstream].[SOL-Build Management],\n[Workstream].[SOL-Data],\n[Workstream].[SOL-Design Management],\n[Workstream].[SOL-Learning],\n[Workstream].[SOL-Platform Management],\n[Workstream].[SOL-Platform Management Basis],\n[Workstream].[SOL-Platform Management Integration],\n[Workstream].[SOL-R\u0026A],\n[Workstream].[SOL-Security \u0026 Authorization],\n[Workstream].[SOL-Solution Core \u0026 Integration],\n[Workstream].[SOL-Solution Methodology],\n[Workstream].[SOL-Technology \u0026 Architecture],\n[Workstream].[SOL-BITC-NFCM]\n})”,“format_string”:“”},{“name”:“ZOQU-Unscripted Test”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\nFilter(\n[Deliverable Doc ID].Members,\n[Deliverable Doc ID].CurrenthierarchyMember.Name MATCHES (".ZOQU.")\n)\n})”,“format_string”:“”},{“name”:“ZOQT-Scripted Test”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\nFilter(\n[Deliverable Doc ID].Members,\n[Deliverable Doc ID].CurrenthierarchyMember.Name MATCHES (".ZOQT.")\n)\n})”,“format_string”:“”},{“name”:“All Scripted/Unscripted Test”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\n[Deliverable Doc ID].[ZOQU-Unscripted Test],\n[Deliverable Doc ID].[ZOQT-Scripted Test]\n})”,“format_string”:“#,##0”},{“name”:“Created After Mar 1st, 2024”,“dimension”:“Time”,“formula”:“Aggregate(\n[Time].[Month].DateMembersBetween(‘Mar 01 2024’, ‘today’)\n)\n”,“format_string”:“”},{“name”:“All But ZOQT”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate(\n Filter(\n [Deliverable Doc ID].Members,\n [Deliverable Doc ID].CurrentHierarchyMember.Name \n NOT MATCHES ".ZOQU.|.N/.|.n/."\n )\n)”,“format_string”:“”},{“name”:“All but N/A”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\nFilter(\n[Deliverable Doc ID].Members,\n[Deliverable Doc ID].CurrenthierarchyMember.Name NOT MATCHES (".N/.")\n)\n})”,“format_string”:“”},{“name”:“ALL tEST 2”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\n[Deliverable Doc ID].[All But ZOQT],\n[Deliverable Doc ID].[All but N/A]\n})”,“format_string”:“”}]
}

selecting filter rows on the measure, not matches, then ZOQU.|.N/.
You can the remove the measure field if you don’t want it (Don’t know if you selected this in your rows.

This does not clear issues even after doing this I was not able to exclude the values in text field.

When I have tried to create below calculated member in the dimension as below
Aggregate({
Filter(
[Deliverable Doc ID].Members,
[Deliverable Doc ID].CurrenthierarchyMember.Name NOT MATCHES (“.ZOQT.”)
)
})

This Worked, but when I add the or condition in the above measure as below
Aggregate(
Filter(
[Deliverable Doc ID].Members,
[Deliverable Doc ID].CurrentHierarchyMember.Name
NOT MATCHES “.ZOQU. |.N/.
)
)
This is not working

when I try to combine both the calculated members as below and select this giving me the error.
Aggregate({
[Deliverable Doc ID].[All But ZOQT],
[Deliverable Doc ID].[All but N/A]
})

Hi @Amzad

You’re on the right path, however, in the Filter function you should specify which level members do you want to iterate through.

Also, the regular expression that you use for matching, needs to be improved to accommodate the forward slash, which is a special symbol in regex.

Please try the following formula:

Aggregate(
  Filter(
    [Deliverable Doc ID].[Deliverable Doc ID].Members,
    [Deliverable Doc ID].CurrentHierarchyMember.Name NOT MATCHES "ZOQU.*|N\/.*"
  )
)

For the other formula this should work as expected:

Aggregate(
  Filter(
  [Deliverable Doc ID].[Deliverable Doc ID].Members,
  [Deliverable Doc ID].CurrentHierarchyMember.Name NOT MATCHES "ZOQT.*"
  )
)

​Let me know if you have any additional questions on this!
​Best regards,
​Nauris