{"id":1743,"date":"2020-09-11T07:19:48","date_gmt":"2020-09-11T07:19:48","guid":{"rendered":"https:\/\/bi.unija.com\/en\/?p=1743"},"modified":"2020-10-22T11:47:31","modified_gmt":"2020-10-22T11:47:31","slug":"filter-context-in-power-bi","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/","title":{"rendered":"Filter Context in Power BI"},"content":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019).<\/em><\/p>\n

Understanding filter context is key when working with Power BI. Filter context and context propagation are important concepts we have to understand, if we really want to understand DAX.<\/p>\n

Initial filter context<\/h1>\n

Initial filter context <\/strong>is a filter that comes from visualizations or other parts of reports. Let’s look at simple example. We have Products<\/strong> table with fields Platform<\/strong> and Storage<\/strong>.<\/p>\n

<\/p>\n

We create a Matrix visualization and select the field Platform<\/strong> to Rows<\/strong> and field Storage<\/strong> to Values<\/strong>.<\/p>\n

<\/p>\n

We get Count of Storage<\/strong> for every Platform<\/strong>. What happened? Every cell inside a table is filtered. First cell causes the table Products<\/strong> to filter, only leaving rows with Platform<\/strong> value PC<\/strong>. Now all the values in Storage<\/strong> column are summed up to the value 107<\/strong>. Similarly the next cell filter table Products<\/strong>, leaving only rows with Platform<\/strong> value PS3<\/strong>. Column Storage<\/strong> is then summed up, returning value 7<\/strong>.<\/p>\n

Filtering happens in the moment of evaluating the cell and is then released.<\/p>\n

Sources of filter context<\/h2>\n

Filter context can come from different parts of a report. We can split them up into four categories:<\/p>\n

    \n
  1. Rows<\/li>\n
  2. Columns<\/li>\n
  3. Slicers<\/li>\n
  4. Filters<\/li>\n<\/ol>\n

    <\/p>\n

    Instead of slicers we could use any other visualizations, since every visualization filters all other visualizations.<\/p>\n

    Initial filter context comes from the 4 groups above. We call it initial because it can later be changed using DAX formula CALCULATE()<\/strong>. Initial filter context always means filtering from upper 4 sources, before filtering with CALCULATE()<\/strong> formula.<\/p>\n

    Filter propagation<\/h1>\n

    Let’s look at a data model that is comprised of dimension tables Products<\/strong> and Projects<\/strong> and fact table Sales<\/strong>.<\/p>\n

    <\/p>\n

    In report view we create a following matrix.<\/p>\n

    <\/p>\n

    Column Count of Storage<\/strong> remains the same as in the example above. But surprisingly column Count of Project<\/strong> has the same value for all platforms. Why? The reason is the row PC<\/strong> or the Platform<\/strong> field only filters the table in which it is located. Because it is located in the table Products<\/strong> it only filters table Products<\/strong>. Table Projects<\/strong> remains unfiltered and returns count of all products for each platform.<\/p>\n

    Filter flow between tables<\/h2>\n

    Let’s add another column to the table above: Value<\/strong> field from table Sales<\/strong>. We get the following table.<\/p>\n

    <\/p>\n

    Even though Sales<\/strong> table contains Value<\/strong> column, table remains unfiltered. Filters flow between tables, but only in the direction of connection.<\/p>\n

    <\/p>\n

    With typical layout of tables (dimension tables above, fact table below) filters always travel down<\/strong>.<\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    For Power BI version 2.76.5678.782 (December 2019). Understanding filter context is key when working with Power BI. Filter context and context propagation are important concepts we have to understand, if we really want to understand DAX. Initial filter context Initial<\/p>\n","protected":false},"author":5,"featured_media":1752,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[4],"tags":[8],"class_list":["post-1743","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-advanced","tag-power-bi"],"acf":[],"yoast_head":"\nFilter Context in Power BI - Bi Unija<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Filter Context in Power BI - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"For Power BI version 2.76.5678.782 (December 2019). Understanding filter context is key when working with Power BI. Filter context and context propagation are important concepts we have to understand, if we really want to understand DAX. Initial filter context Initial\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2020-09-11T07:19:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-22T11:47:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/09\/24eng.png\" \/>\n\t<meta property=\"og:image:width\" content=\"720\" \/>\n\t<meta property=\"og:image:height\" content=\"480\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Branka Trifunovi\u0107\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Branka Trifunovi\u0107\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/\",\"url\":\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/\",\"name\":\"Filter Context in Power BI - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/09\/24eng.png\",\"datePublished\":\"2020-09-11T07:19:48+00:00\",\"dateModified\":\"2020-10-22T11:47:31+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/09\/24eng.png\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/09\/24eng.png\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Filter Context in Power BI\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bi.unija.com\/en\/#website\",\"url\":\"https:\/\/bi.unija.com\/en\/\",\"name\":\"Bi Unija\",\"description\":\"Just another Bi site\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bi.unija.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\",\"name\":\"Branka Trifunovi\u0107\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"caption\":\"Branka Trifunovi\u0107\"},\"url\":\"https:\/\/bi.unija.com\/en\/author\/branka\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Filter Context in Power BI - Bi Unija","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/","og_locale":"en_US","og_type":"article","og_title":"Filter Context in Power BI - Bi Unija","og_description":"For Power BI version 2.76.5678.782 (December 2019). Understanding filter context is key when working with Power BI. Filter context and context propagation are important concepts we have to understand, if we really want to understand DAX. Initial filter context Initial","og_url":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/","og_site_name":"Bi Unija","article_published_time":"2020-09-11T07:19:48+00:00","article_modified_time":"2020-10-22T11:47:31+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/09\/24eng.png","type":"image\/png"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/","url":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/","name":"Filter Context in Power BI - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/09\/24eng.png","datePublished":"2020-09-11T07:19:48+00:00","dateModified":"2020-10-22T11:47:31+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/09\/24eng.png","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/09\/24eng.png","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/filter-context-in-power-bi\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"Filter Context in Power BI"}]},{"@type":"WebSite","@id":"https:\/\/bi.unija.com\/en\/#website","url":"https:\/\/bi.unija.com\/en\/","name":"Bi Unija","description":"Just another Bi site","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bi.unija.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10","name":"Branka Trifunovi\u0107","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","caption":"Branka Trifunovi\u0107"},"url":"https:\/\/bi.unija.com\/en\/author\/branka\/"}]}},"_links":{"self":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1743"}],"collection":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/comments?post=1743"}],"version-history":[{"count":1,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1743\/revisions"}],"predecessor-version":[{"id":1751,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1743\/revisions\/1751"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1752"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1743"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1743"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1743"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}