{"id":5969,"date":"2022-09-02T09:30:24","date_gmt":"2022-09-02T09:30:24","guid":{"rendered":"https:\/\/www.stl-training.co.uk\/b\/?p=5969"},"modified":"2023-12-30T23:03:16","modified_gmt":"2023-12-30T23:03:16","slug":"maintain-a-competitive-edge-by-exploiting-dax-in-power-bi","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/maintain-a-competitive-edge-by-exploiting-dax-in-power-bi\/","title":{"rendered":"Maintain a competitive edge by exploiting DAX in Power BI"},"content":{"rendered":"<p>DAX in <a href=\"https:\/\/www.stl-training.co.uk\/ao\/71\/microsoft-power-bi-training-courses-london-uk.html\">Power BI Desktop<\/a> is an amazing tool that allows you to fully analyse your data giving you real insights to achieve profitable outcomes.<\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-5970 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin1.png\" alt=\"\" width=\"455\" height=\"314\" srcset=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin1.png 455w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin1-300x207.png 300w\" sizes=\"(max-width: 455px) 100vw, 455px\" \/><\/p>\n<p>You can get a lot of Business Intelligence from your reports without any <a href=\"https:\/\/www.stl-training.co.uk\/syl\/356\/dax-bi-training-courses-london.html\">DAX<\/a> whatsoever. However, you may want specific information that is only possible to obtain with DAX. For example, % Sales Growth Year on Year by Region or a Rolling Last 12 month total. In this article you will find out what DAX means, what it can be used for and how it can help you to gain a competitive advantage in your data analysis.<\/p>\n<h2><strong>What is DAX and why is it so useful?<\/strong><\/h2>\n<p>DAX\u00a0stands for\u00a0Data Analysis Expressions. It is a formula language used to optimise the performance of data analysis in Power BI Desktop. One of its main benefits is being able to re-use it numerous times. This means faster processing time since a calculation used in another calculation will not need to be reprocessed because the first calculation is already stored in memory. Consequently, end users can make instant business decisions more efficiently as they receive their updated reports. Moreover, the greater flexibility in using DAX means that end users will have a competitive edge over those who only use standard analysis tools.<\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-5974 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin5.jpg\" alt=\"\" width=\"1920\" height=\"1080\" srcset=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin5.jpg 1920w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin5-300x169.jpg 300w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin5-1024x576.jpg 1024w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin5-768x432.jpg 768w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin5-1536x864.jpg 1536w\" sizes=\"(max-width: 1920px) 100vw, 1920px\" \/><\/p>\n<h2><strong>How to get started<\/strong><\/h2>\n<p>The world of DAX is vast and can get complicated especially if the data is spread across many tables. To keep things simple, we will look at a practical example drawing data from a single dataset. Let\u2019s say you had some global sales data and you needed to show the Average number of Orders per Country. To achieve this, we will look at creating 3 DAX measures:<\/p>\n<ul>\n<li style=\"list-style-type: none\">\n<ul>\n<li style=\"list-style-type: none\">\n<ul>\n<li>Total number of Orders (measure 1)<\/li>\n<li>Number of Countries (measure 2)<\/li>\n<li>Average Orders per Country (measure 3)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>The first two DAX measures will be created separately and then combined to make the third measure thus speeding up data processing time.<\/p>\n<p>&nbsp;<\/p>\n<ol>\n<li style=\"list-style-type: none\">\n<ol>\n<li style=\"list-style-type: none\">\n<ol>\n<li>Import a single Excel file e.g. \u2018Global Food Sales\u2019 into Power BI Desktop<\/li>\n<li>On the ribbon go to HOME &gt; NEW MEASURE<\/li>\n<li>Type the following:<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p>Number of Orders = COUNT(FoodSales[Order ID])<\/p>\n<p>&nbsp;<\/p>\n<p>This calculates a total count of all orders where \u2018FoodSales\u2019 is the name of the table and OrderID is the field name (note the requirement for square brackets as it is a field)<\/p>\n<p>&nbsp;<\/p>\n<ol>\n<li style=\"list-style-type: none\">\n<ol>\n<li style=\"list-style-type: none\">\n<ol start=\"4\">\n<li>Create a \u2018Card\u2019 visual to show the total number of orders:<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p><img decoding=\"async\" class=\"wp-image-5971 size-full aligncenter\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin2.png\" alt=\"\" width=\"152\" height=\"114\" srcset=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin2.png 152w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin2-150x114.png 150w\" sizes=\"(max-width: 152px) 100vw, 152px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ol>\n<li style=\"list-style-type: none\">\n<ol>\n<li style=\"list-style-type: none\">\n<ol start=\"5\">\n<li>Create another new measure and type:<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p>Number of Countries = DISTINCTCOUNT(FoodSales[Country])<\/p>\n<p>&nbsp;<\/p>\n<p>This calculates a unique number of countries out of a list of multiple\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0entries for each country<\/p>\n<p>&nbsp;<\/p>\n<ol>\n<li style=\"list-style-type: none\">\n<ol>\n<li style=\"list-style-type: none\">\n<ol start=\"6\">\n<li>Create another Card visual to show the number of countries:<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p><img decoding=\"async\" class=\"size-full wp-image-5972 aligncenter\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin3.png\" alt=\"\" width=\"169\" height=\"117\" \/><\/p>\n<p><span style=\"font-size: 1rem\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a07. Create a third measure and type:<\/span><\/p>\n<p>Average Orders per Country =<\/p>\n<p>DIVIDE([Number of Orders],[Number of Countries])<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>where the [Number of Orders] is the \u2018Numerator\u2019<\/li>\n<\/ul>\n<p>and [Number of Countries] is the \u2018Denominator\u2019<\/p>\n<p>&nbsp;<\/p>\n<ol>\n<li style=\"list-style-type: none\">\n<ol>\n<li style=\"list-style-type: none\">\n<ol start=\"8\">\n<li>Format this measure to no decimal places<\/li>\n<li>Again add this measure to a Card:<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p><img decoding=\"async\" class=\"wp-image-5973 size-full aligncenter\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin4.png\" alt=\"\" width=\"208\" height=\"150\" \/><\/p>\n<p>If you add any new Countries to future sales data, this average will automatically update.<\/p>\n<p>&nbsp;<\/p>\n<h2><strong>Conclusion<\/strong><\/h2>\n<p>Gaining a competitive edge in the marketplace is key to being more profitable. The ability to fully exploit DAX in Power BI Desktop will definitely help you to achieve this goal.<\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-5975 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin6.jpg\" alt=\"\" width=\"1920\" height=\"1080\" srcset=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin6.jpg 1920w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin6-300x169.jpg 300w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin6-1024x576.jpg 1024w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin6-768x432.jpg 768w, https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2022\/08\/DAXMartin6-1536x864.jpg 1536w\" sizes=\"(max-width: 1920px) 100vw, 1920px\" \/><\/p>\n<p>For more articles on Data Analysis and practical uses, please click below:<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/easily-track-performance-with-analytical-lines-in-power-bi\/\">https:\/\/www.stl-training.co.uk\/b\/easily-track-performance-with-analytical-lines-in-power-bi\/<\/a><\/p>\n<p><a href=\"https:\/\/www.forbes.com\/search\/?q=data%20analytics&amp;sh=288804da279f\" class=\"broken_link\">https:\/\/www.forbes.com\/search\/?q=data%20analytics&amp;sh=288804da279f<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>DAX in Power BI Desktop is an amazing tool that allows you to fully analyse your data giving you real insights to achieve profitable outcomes. You can get a lot of Business Intelligence from your reports without any DAX whatsoever. However, you may want specific information that is only possible to obtain with DAX. For [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[8,650],"tags":[140,664],"class_list":["post-5969","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-data","tag-dax"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/5969","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=5969"}],"version-history":[{"count":2,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/5969\/revisions"}],"predecessor-version":[{"id":5979,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/5969\/revisions\/5979"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=5969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=5969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=5969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}