{"id":6061,"date":"2024-08-16T12:26:13","date_gmt":"2024-08-16T12:26:13","guid":{"rendered":"https:\/\/powergi.net\/?p=6061"},"modified":"2024-08-26T03:55:03","modified_gmt":"2024-08-26T03:55:03","slug":"lookup-by-keyword-in-dax-contains-logic-power-bi-and-power-pivot","status":"publish","type":"post","link":"https:\/\/powergi.net\/de\/blog\/lookup-by-keyword-in-dax-contains-logic-power-bi-and-power-pivot\/","title":{"rendered":"Lookup by keyword in DAX | Contains Logic | Power BI and Power Pivot"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

We want to analyze how invoicing works in our company, and we would like to find out which service category is the one we\u2019re selling the most and which we could improve. So, we pull data related to all invoices we\u2019ve sent to clients, and we find the following table:<\/span><\/p>

\t\t
\n\t\t\t
\n\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Turn your ideas into digital solutions<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t

Our team guides you step by step to build custom apps in Power Platform.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"Invoice\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tAs you notice, invoice line description is not consistent or standard across invoice details so if we want to use the column as is, it will be difficult to analyze data properly.\u00a0<\/span>\n\nWe need to categorize each row into the 4 following groups:\u00a0<\/span>\n
    \n \t
  1. Adjustments\u00a0<\/span><\/li>\n \t
  2. After Hours Labor\u00a0<\/span><\/li>\n \t
  3. Projects\u00a0<\/span><\/li>\n \t
  4. Ongoing support\u00a0<\/span><\/li>\n<\/ol>\nGoing forward we can work in standardizing how invoices are issued and which text we indicate in the invoice line, but for historic data we need to apply some rules to categorize into the groups listed above.\u00a0<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t

    Create a Category \u2013 Keyword table <\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tFirst, let\u2019s think about the rules we want to apply. For example, if line description contains \u201cProject \u2013 \u201c then we want to categorize this as \u201cProjects\u201d, or if it has the keyword \u201cAdjustment\u201d, then this line should go to \u201cAdjustment\u201d, and so on. For this scenario we will focus on the following four groups and corresponding keywords<\/span>\u00a0<\/span>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"Create\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t

    If different words map to a single category, just add a new row in the table for each of the different variations the group could have:\u00a0<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"Create\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t

    Add a calculated column in the invoice lines table \n<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t

    We have two tables:\u00a0<\/span><\/p>

    1. Invoice Details\u00a0<\/span><\/li>
    2. Keywords<\/span>
      <\/span><\/li><\/ol>

      We want to check the \u201cinvoice_description\u201d column in each of the rows and see if any of the keywords listed in the Keywords table are contained in this description.\u00a0\u00a0<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

      \n\t\t\t\t
      \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"Invoice\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t\t\t

      If there is a keyword that matches, we want to get which category the match is for. <\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

      \n\t\t\t\t
      \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"Code\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t\t\t

      Code used for this calculated column: <\/span><\/p>

      \t\t
      \n\t\t\t
      \n\t\t
      \n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t

      Are you ready to discover the joy of automation?<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

      \n\t\t\t\t
      \n\t\t\t\t\t

      Whether you have a project in mind or just want to know how we can help, we\u2019re happy to have a conversation<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t

      \n\t\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t
      \n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t
      \n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t
      \n\t\t\t
      \n\t\t\t\t\n\t\t\t\t\tBilling Category =   \r\n\/\/current line text, to lowercase \r\nVAR currentrowtext = \r\n\u202f \u202f LOWER ( 'Invoice Details'[invoice_description] ) \r\n\/\/search all keywords and find if any match \r\nVAR searchrow = \r\n\u202f \u202f MINX ( \r\n\u202f \u202f \u202f \u202f FILTER ( \r\n\u202f \u202f \u202f \u202f \u202f \u202f Keywords, \r\n\u202f \u202f \u202f \u202f \u202f \u202f SEARCH (Keywords[Keyword], currentrowtext, 1, 0 )  \r\n\u202f \u202f \u202f \u202f \u202f \u202f \/\/search current row in the keywords table and filter if there is any match \r\n\u202f \u202f \u202f \u202f ), \r\n\u202f \u202f \u202f \u202f Keywords[Category] \r\n\u202f \u202f \u202f \u202f \/\/return the first found category  \r\n\u202f \u202f )  \r\n\/\/check if there is any match, if not, return \"Other\" \r\nRETURN \r\n\u202f \u202f COALESCE ( searchrow, \"Other\" ) \r\n<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-ff55e27 elementor-widget elementor-widget-heading\" data-id=\"ff55e27\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">Use the new column in your visualizations <\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-7111a42 elementor-widget elementor-widget-text-editor\" data-id=\"7111a42\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">Now we\u2019re more than ready to start analyzing our data with the new category table we added!\u00a0<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-fcaeae3 elementor-widget elementor-widget-image\" data-id=\"fcaeae3\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"359\" height=\"168\" src=\"https:\/\/powergi.net\/wp-content\/uploads\/2024\/08\/unnamed-63.png\" class=\"attachment-large size-large wp-image-6069\" alt=\"Analyze the New Category Table\" srcset=\"https:\/\/powergi.net\/wp-content\/uploads\/2024\/08\/unnamed-63.png 359w, https:\/\/powergi.net\/wp-content\/uploads\/2024\/08\/unnamed-63-300x140.png 300w\" sizes=\"(max-width: 359px) 100vw, 359px\">\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>We want to analyze how invoicing works in our company, and we would like to find out which service category is the one we\u2019re selling the most and which we could improve. So, we pull data related to all invoices we\u2019ve sent to clients, and we find the following table: Turn your ideas into digital [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":6057,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"glossary_letter":"","footnotes":""},"categories":[35,118],"tags":[13,16,154,17,18],"class_list":["post-6061","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft-excel","category-sharepoint-cat","tag-automation","tag-excel","tag-invoicing","tag-power-automate","tag-power-bi"],"glossary_letter":null,"rank_math_title":"Power BI | Power Pivot | Power GI","rank_math_description":"Learn to categorize invoice data in Power BI and Power Pivot using DAX keyword lookup. Standardize descriptions, optimize analysis, and improve invoicing. Call us!","rank_math_focus_keyword":"Power BI","_links":{"self":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts\/6061","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/comments?post=6061"}],"version-history":[{"count":0,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts\/6061\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/media\/6057"}],"wp:attachment":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/media?parent=6061"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/categories?post=6061"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/tags?post=6061"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}