<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Mitalee on Medium]]></title>
        <description><![CDATA[Stories by Mitalee on Medium]]></description>
        <link>https://medium.com/@mitalee?source=rss-269823cedaee------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*F_k8irQ3R3OxRnso2yuwew.jpeg</url>
            <title>Stories by Mitalee on Medium</title>
            <link>https://medium.com/@mitalee?source=rss-269823cedaee------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 22:11:52 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mitalee/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Macros in Excel]]></title>
            <link>https://medium.com/@mitalee/macros-in-excel-0e064d8db0c9?source=rss-269823cedaee------2</link>
            <guid isPermaLink="false">https://medium.com/p/0e064d8db0c9</guid>
            <category><![CDATA[vba]]></category>
            <category><![CDATA[vba-macros]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[macro]]></category>
            <dc:creator><![CDATA[Mitalee]]></dc:creator>
            <pubDate>Wed, 19 Jun 2024 05:26:11 GMT</pubDate>
            <atom:updated>2024-06-19T05:26:11.178Z</atom:updated>
            <content:encoded><![CDATA[<p>What is a Macro?</p><p>In Excel, a macro refers to a set of instructions and operations that are recorded and useful for use as many times as required. It assists in reducing nuisance activities and time wastage since the software completes the work promptly and accurately.</p><p>How Macros Work</p><p>Macros are also written in Visual Basic for Applications (VBA) which is a language mostly related to Excel and other applications owned by Microsoft Office. Usually, if you wanted to record the steps you’re making into VBA code, then recording a macro in Excel is your best shot. There are options for you to click the macro and it will perform the activities for the specific number of times you set.</p><h3><strong>1. Macro to Format Phone Numbers in Excel</strong></h3><h4>1. Enable the Developer Tab</h4><ul><li>Open Excel.</li><li>Go to File &gt; Options.</li><li>Check the box for Developer in the right pane.</li><li>Click on Use Relative References (It performs the same set of actions on different cells or ranges, depending on where it is run from.)</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6tOd1BrURklbB2UMoy3nbg.png" /></figure><h4>2. Record the Macro</h4><ul><li>In the Record Macro dialog box:</li><li><strong>Macro name:</strong> Enter PhoneNumbersCan.</li><li><strong>Shortcut key:</strong> (Optional) Assign a shortcut key if desired.</li><li><strong>Store macro in:</strong> Choose This Workbook.</li><li>Click OK.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_MSkxpifK-BQbbRM5PbSrw.png" /></figure><p><strong>3. Customize the number</strong></p><ul><li>Got to &lt;p style=”background-color: #f7f7f7; border: 1px solid #ccc; padding: 10px 20px; border-radius: 10px; display: inline-block; font-weight: bold; box-shadow: 2px 2px 5px rgba(0, 0, 0, 0.1);”&gt;<br> Home<br>&lt;/p&gt;tab</li><li>Find the number formatting command group and click on the small button on the lower end of the command group.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RX53RIN65QQi_SDhhaRJbA.png" /></figure><ul><li>Custom format phone numbers to (+1)###-###-####</li><li>Click OK</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bXdoLhlg1GLKc-5TnHP3Gw.png" /></figure><p><strong>4. Stop recording the Macro</strong></p><ul><li>Go back to the Developer tab.</li><li>Click on Stop Recording.</li></ul><p><strong>5. Enable the Macro</strong></p><ul><li>Click on the first phone cell and then ctrl+shift+down to select everything</li><li>Use the keyboard shortcut created for the macro ctrl+shift+P</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0lZSC0ue-_WdJ5w1Mf7T0Q.png" /></figure><p>And thats how you efficiently format phone numbers in excel.</p><h3>2. Remove Birth year from Date</h3><p><strong>1. Enable the Developer Tab</strong></p><ul><li>Open Excel.</li><li>Go to File &gt; Options.</li><li>Check the box for Developer in the right pane.</li><li>Click on Use Relative References (It performs the same set of actions on different cells or ranges, depending on where it is run from.)</li></ul><h4>2. Record the Macro</h4><ul><li>In the Record Macro dialog box:</li><li><strong>Macro name:</strong> Enter BirthyearNot.</li><li><strong>Shortcut key:</strong> (Optional) Assign a shortcut key if desired.</li><li><strong>Store macro in:</strong> Choose This Workbook.</li><li>Click OK.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oBj420YZvCAhjVQOzeULag.png" /></figure><p><strong>3. Customize the date format</strong></p><ul><li>Got to Home tab</li><li>Find the number formatting command group and click on the small button on the lower end of the command group.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RX53RIN65QQi_SDhhaRJbA.png" /></figure><ul><li>Custom format date to mmmm dd</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hQ0rnszvRqh6uLswiuddAQ.png" /></figure><p><strong>4. Stop recording the Macro</strong></p><ul><li>Go back to the Developer tab.</li><li>Click on Stop Recording.</li></ul><p><strong>5. Enable the Macro</strong></p><ul><li>Click on the first phone cell and then ctrl+shift+down to select everything</li><li>Use the keyboard shortcut created for the macro ctrl+shift+b</li></ul><p><strong>3. Record a Macro for Sorting</strong><br><strong>1. Enable the Developer Tab</strong></p><ul><li>Open Excel.</li><li>Go to File &gt; Options.</li><li>In the Excel Options dialog box, click Customize Ribbon.</li><li>Check the box for Developer in the right pane.</li><li>Click OK.</li></ul><p><strong>2. Start Recording the Macro</strong></p><ul><li>Go to the Developer tab.</li><li>Click on Record Macro.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i1fu2mNm9gbeI0eubU1LeA.png" /></figure><p><strong>3. Record the Macro for Sorting</strong></p><ul><li>In the Record Macro dialog box:</li><li>Macro name: Enter Sortdept.</li><li>Shortcut key: (Optional) Assign a shortcut key if desired.</li><li>Store macro in: Choose This Workbook.</li><li>Description: Leave empty</li><li>Click OK.</li></ul><p><strong>4. Perform the Sorting Operation</strong></p><ul><li>Select the Range:</li><li>Click and drag to select the range of data you want to sort.</li><li>Example: Select By Department.</li><li>Sort the Data:</li><li>Go to the Data tab.</li><li>Click on Sort.<br>In the Sort dialog box, choose the column you want to sort by.<br>Select the sort order (e.g., Ascending or Descending, A to Z,etc).</li><li>Click OK.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iDFcYzha8aYpha3jP6wuYw.png" /></figure><p><strong>5. Stop Recording the Macro</strong></p><ul><li>Go back to the Developer tab.</li><li>Click on Stop Recording.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U1Erm_TDY9W3Cyqlx60SpA.png" /></figure><p><strong>4.Record a Macro for Filter</strong></p><p><strong>1. Enable the Developer Tab</strong></p><ul><li>Open Excel.</li><li>Go to File &gt; Options.</li><li>In the Excel Options dialog box, click Customize Ribbon.</li><li>Check the box for Developer in the right pane.</li><li>Click OK.</li></ul><p><strong>2. Start Recording the Macro</strong></p><ul><li>Go to the Developer tab.</li><li>Click on Record Macro.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zfU18Ggo25IV4CI6WiUSmw.png" /></figure><p><strong>3. Record the Macro for Filtering</strong></p><ul><li>In the Record Macro dialog box:</li><li>Macro name: Enter FilterData.<br>Shortcut key: (Optional) Assign a shortcut key if desired.<br>Store macro in: Choose This Workbook.<br>Description: Leave it empty.</li><li>Click OK.</li></ul><p><strong>4. Perform the Filtering Operation</strong></p><ul><li>Apply the Filter:</li><li>Go to the Data tab.</li><li>Click on Filter.</li><li>Click the drop-down arrow in the column header you want to filter.</li><li>Select the criteria for filtering (e.g., uncheck Select All and check only Logistics).</li><li>Click OK.</li></ul><p><strong>5. Stop Recording the Macro</strong></p><ul><li>Go back to the Developer tab.</li><li>Click on Stop Recording.</li></ul><p>These were some basic macros used in Excel for automation and making our lives easier. The next blog will have some advanced macros used for calculations in the report. Stay tuned!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0e064d8db0c9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploring Python Libraries for Data Analytics]]></title>
            <link>https://medium.com/@mitalee/exploring-python-libraries-for-data-analytics-d176359cf83f?source=rss-269823cedaee------2</link>
            <guid isPermaLink="false">https://medium.com/p/d176359cf83f</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[visulaization]]></category>
            <category><![CDATA[matplotlib]]></category>
            <category><![CDATA[seaborn]]></category>
            <dc:creator><![CDATA[Mitalee]]></dc:creator>
            <pubDate>Mon, 27 May 2024 02:30:38 GMT</pubDate>
            <atom:updated>2024-05-27T02:30:38.232Z</atom:updated>
            <content:encoded><![CDATA[<p>In the fast-moving world of data analytics, having the right tools means all the difference. With its rich ecosystem of libraries, Python has turned out to be a strong powerhouse for data analysis tasks and offers a great variety of tools to help solve any problem. In this post, we will be going deep into the most commonly used Python libraries for analytics, covering the features, use cases, and benefits of each.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8hD7RZgPM3SkgCDmf7UvrA.jpeg" /><figcaption>Python libraries used for Analysis</figcaption></figure><h3><strong>NumPy</strong></h3><p>One cannot underestimate the importance of NumPy in scientific computing and, in general, in programming Python. Its main feature is ndarray — the primary and tremendously effective array that operates with big data. NumPy provides a wealth of linear operations that can be applied to arrays, as well as ability to perform Fourier analysis, work with random numbers, and utilize linear algebraic equations. NumPy is a library for the Python programming language that is used to solve mathematical algorithms and perform calculations, whether simple arithmetic or solving equations, or modeling other systems mathematically.</p><p><strong>Use cases:</strong> Operations on vectors and matrices, solutions to linear programs, obtaining discrete and Fourier transforms.</p><p><strong>Advantages:</strong> The speed and convenience of working with arrays, a huge number of mathematical functions for working with NumPy data structures, integration with other libraries of the Python language.</p><h3><strong>pandas</strong></h3><p>When it comes to data manipulation and processing pandas is the most widely used tool in the python programming environment. Extension of NumPy, pandas provides data structures, namely Series and DataFrame, and functions for data wrangling and analysis. It offers halting commands for such tasks as reading and writing different file formats, handling missing values, and reshaping datasets.</p><p><strong>Use cases: </strong>Data pre-processing, data transformation, data visualization, and data analysis.</p><p><strong>Advantages: </strong>Data flexibility enhanced manipulation, simple syntax of codes, and integration with other Python libraries.</p><h3><strong>Matplotlib</strong></h3><p>Visualization plays an essential role in making sense of datasets, and Matplotlib is the standard library for a 2D plotting in the Python programming language environment. Matplotlib is a programming language’s treasure trove for creators to make diverse graphs like a line graph, scatter graph, histogram, bar graphs, and much more. From analyzing trends in your data or given datasets to presenting them to stakeholders or preparing graphics for publication, Matplotlib comes in handy.</p><p><strong>Use cases:</strong> This information can include anyone or a combination of the following: data visualization, exploratory data analysis, presentation of results.</p><p><strong>Advantages:</strong> advanced plotting, rendition versatility, and compatibility with different output formats.</p><h3><strong>Seaborn</strong></h3><p>Seaborn is a library based on the Matplotlib that provides the functionality to generate high-level graphic visualizations with statistical representation in Python. Of the many utilities of Seaborn, it is the ability to generate a noticeable pattern or a peculiar outlier from data that makes the library incredibly efficient._payments Seaborn facilitates the use of scatter plots and regression lines, box plots, and violin plots. Regardless of testing for correlation, distribution, or group differences, Seaborn offers not only an extensive variety of statistical graphics for Python but also creates an insight into the complex world of data analysis for everyone.</p><p><strong>Use cases:</strong> Introduction of the data, special attention to visualization, report of the findings.</p><p><strong>Advantages:</strong> As compared to Standard MATLAB, TeleMAT gains advantage in the following aspects: easy for programming, good looking defaults, targeting specific plots.</p><h3><strong>scikit-learn</strong></h3><p>If you are a fan of machine learning, you will find scikit-learn as a gold mine repository of numerous algorithms and tools for predicting models, classifiers, regressors, clustering, and feature elimination. Scikit-learn is a widely used Python library for machine learning that helps to build and tune models easily due to a clear and easy-to-use API coupled with comprehensive documentation. Regardless, whether you’re trying your hand at your first machine learning project or are already tuning models for production, scikit-learn has the resources you need.</p><p><strong>Use cases:</strong> Supervised learning, unsupervised learning, classification, regression analysis, K-means clustering.</p><p><strong>Advantages:</strong> Rich repertoire of algorithms, intuitive interface, and powerful documentation.</p><p>The ecosystem of Python today provides an extensive variety of libraries that will continue to help Data Scientists, Analysts, and interested hobbyists in the course of their data analysis tasks. In data analysis, there are several tasks such as importing data, cleaning data, visualizing data, and establishing models; all of which has an associated python library. There are many exciting possibilities for your data analysis adventure and knowledge of these libraries will help you shape your data understanding process to your desired goals efficiently and effectively.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d176359cf83f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering Time Intelligence Functions in DAX]]></title>
            <link>https://medium.com/@mitalee/mastering-time-intelligence-functions-in-dax-77c55186c60b?source=rss-269823cedaee------2</link>
            <guid isPermaLink="false">https://medium.com/p/77c55186c60b</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[timeseries]]></category>
            <category><![CDATA[visulaization]]></category>
            <dc:creator><![CDATA[Mitalee]]></dc:creator>
            <pubDate>Thu, 16 May 2024 05:34:37 GMT</pubDate>
            <atom:updated>2024-05-16T05:34:37.177Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K_JJCQBabp-uQYTvWSPSqw.png" /></figure><p>Time is the most important dimension in data analysis, since it helps us to compare trends, patterns, and performance across various time periods. In Microsoft Power BI and Excel, after a user has learned the Time Intelligence Functions in DAX (Data Analysis Expressions), they have the ability to do complicated time-based calculations and comparisons. This complete guide will be devoted to the Time Intelligence Functions in DAX exploring their power and flexibility, as well as giving practical examples and knowledge to develop your data analysis skills.</p><p><strong>Understanding Time Intelligence:</strong> Time Intelligence is the process of analyzing and comparing sets of data that cover different time periods, e.g. days, months, quarters, and years. Time Intelligence DAX Functions allow users to perform dynamic calculations on the base of dates context, for example, year-to-date totals, moving averages, and year-over-year comparisons.</p><p><strong>Common Time Intelligence Functions:</strong></p><p><strong>1. TOTALYTD</strong>: Summarizes the year-to-date total for a given expression by using the filter context of the current moment.</p><p>Syntax:<br>TOTALYTD(&lt;expression&gt;, &lt;dates&gt;[, &lt;filter&gt;])</p><p>Example:<br>TotalSalesYTD = TOTALYTD(SUM(Sales[Amount]), &#39;Date&#39;[Date])</p><p>In this example:</p><ul><li>SUM(Sales[Amount]) represents the expression for the total sales amount.</li><li>&#39;Date&#39;[Date] specifies the date column from the &#39;Date&#39; table in the data model.</li><li>There’s no additional filter applied in this example, but you can add a filter expression as the third parameter if needed.</li></ul><p><strong>2. SAMEPERIODLASTYEAR:</strong> The data is reported for the same period in the preceding year, which enables year-over-year comparison.</p><p>Syntax:<br>SAMEPERIODLASTYEAR(&lt;dates&gt;)</p><p>Example:<br>CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(&#39;Date&#39;[Date]))</p><p>In this example:</p><ul><li>SUM(Sales[Amount]) represents the expression for the total sales amount.</li><li>&#39;Date&#39;[Date] specifies the date column from the &#39;Date&#39; table in the data model.</li></ul><p>The SAMEPERIODLASTYEAR function is often used in conjunction with other DAX functions, such as CALCULATE, to perform year-over-year comparisons or analyze trends over time.</p><p><strong>3.</strong> <strong>DATESBETWEEN:</strong> Forms a table of dates from input dates to two specified dates, which is useful when creating date ranges for analysis.</p><p>Syntax:<br>DATESBETWEEN(&lt;dates&gt;, &lt;start_date&gt;, &lt;end_date&gt;)</p><p>Example:<br>SalesInRange = CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(&#39;Date&#39;[Date], DATE(2023, 1, 1), D</p><p>In this example:</p><ul><li>SUM(Sales[Amount]) represents the expression for the total sales amount.</li><li>&#39;Date&#39;[Date] specifies the date column from the &#39;Date&#39; table in the data model.</li><li>DATE(2023, 1, 1) and DATE(2023, 12, 31) specify the start and end dates of the date range, respectively.</li></ul><p>The DATESBETWEEN function is commonly used to filter data based on specific date ranges for analysis, reporting, or visualization purposes.</p><p><strong>4.</strong> <strong>DATEADD:</strong> Adds or deducts a specified number of units (days, months, years etc. ) to a given date.</p><p>Syntax:<br>DATEADD(&lt;start_date&gt;, &lt;number_of_intervals&gt;, &lt;interval&gt;)</p><p>Example:<br>FutureDate = DATEADD(&#39;Date&#39;[Date], 7, DAY)</p><p>In this example:</p><ul><li>&#39;Date&#39;[Date] specifies the start date from the &#39;Date&#39; table in the data model.</li><li>7 represents the number of days to add to the start date.</li><li>DAY specifies the interval type (in this case, days).</li></ul><p>The DATEADD function is commonly used for date calculations and manipulations in DAX, such as calculating future or past dates, creating date ranges, and performing date-based analysis.</p><p><strong>5. CALENDARAUTO:</strong> The date table is created dynamically in response to the data in the model which makes the date calculations correct and flexible.</p><p>Syntax:<br>CALENDARAUTO()</p><p>Example:<br>DateTable = CALENDARAUTO()</p><p>In this example, the DateTable will be populated with a range of dates based on the dates present in the data model. The date range will automatically adjust as new data is added or existing data is modified.</p><p><strong>Practical Applications of Time Intelligence Functions:</strong></p><p><strong><em>Year-to-Date (YTD) Analysis:</em></strong><em> </em>To see the progress of measures such as sales, revenue, or expenses for the year to date, provide information on performance trends over the current year.</p><p><strong><em>Moving Averages:</em></strong> Use moving averages to filter the data and reveal any trends or patterns that might be hidden behind the fluctuations.</p><p><strong><em>Quarterly and Yearly Comparisons:</em></strong> Compare performance metrics from different periods of time, including quarters or years, to measure progress, identify seasonality, and make prudent decisions.</p><p><strong><em>Rolling Period Analysis:</em></strong> Analyze data over a fixed-period basis, for instance, the previous 7 days or the last 12 months, to determine recent trends and identify new patterns.</p><p><strong>Advanced Time Intelligence Techniques:</strong></p><p><strong><em>Handling Non-Standard Fiscal Years:</em></strong> Extend the Time Intelligence Functions to support the non-standard financial years or financial calendars that are widely used by organizations.</p><p><strong><em>Custom Time Intelligence Calculations:</em></strong> Provide custom-made Time Intelligence calculations that can be configured to fit individual business needs, for example, comparing performance versus custom benchmarks or targets.</p><p><strong><em>Comparing Performance Across Date Hierarchies: </em></strong>The user needs to navigate the hierarchy of dates (e. g. , day, month, quarter, year) to carry out further depth comparison and analysis.</p><p>The DAX Time Intelligence Functions are very useful in order to perform dynamic and insightful analysis related to dates. Through mastering functionalities, analysts can gain vital insights into trends, patterns, and performance over time, thus supporting data driven decision making and strategic planning.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=77c55186c60b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Top 5 DAX (Data Analysis Expressions)]]></title>
            <link>https://medium.com/@mitalee/top-5-dax-data-analysis-expressions-59b25cf2b0e0?source=rss-269823cedaee------2</link>
            <guid isPermaLink="false">https://medium.com/p/59b25cf2b0e0</guid>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Mitalee]]></dc:creator>
            <pubDate>Sat, 30 Mar 2024 05:49:44 GMT</pubDate>
            <atom:updated>2024-03-30T05:49:44.727Z</atom:updated>
            <content:encoded><![CDATA[<p>Top 5 DAX that are useful for Data Manipulation and Analysis. As a Data Analyst it is crucial to know where and which DAX query to use. What is DAX, you might ask? Think of DAX as the engine that drives the analysis and calculations within Excel and Power BI. It’s a powerful formula language designed to perform calculations, manipulate data, and create sophisticated measures and metrics. Whether you’re a seasoned data analyst or just dipping your toes into the world of business intelligence, understanding DAX can unlock a whole new level of analytical capabilities.</p><p>So why should you use DAX?</p><p><strong>Efficiency:</strong> DAX empowers users to effortlessly perform intricate calculations and data manipulations. By utilizing its array of functions and operators, users can streamline their analytical workflows, ultimately saving valuable time.</p><p><strong>Flexibility:</strong> With DAX, users enjoy the freedom to craft custom calculations that precisely meet their unique business needs. Whether it’s computing year-over-year growth, conducting trend analyses, or deriving profitability metrics, DAX offers versatile solutions.</p><p><strong>Scalability:</strong> DAX seamlessly adapts to the evolving analytical demands of users. Whether handling modest datasets or processing vast volumes of data spanning millions of rows, DAX ensures efficient calculations regardless of scale.</p><p><strong>Integration:</strong> DAX seamlessly integrates with Excel and Power BI, the cornerstones of data analysis and visualization. Mastering DAX unlocks the full potential of these tools, enabling users to craft compelling reports and dashboards that facilitate informed decision-making.</p><p><strong>TOP 5 DAX Functions</strong></p><p><strong>1. CALCULATE</strong></p><p>CALCULATE is used to modify the context in which a calculation is made. It evaluates an expression in a context modified by filters. This function is crucial for applying additional filters or modifying the context of calculations.</p><p>Syntax:</p><p>CALCULATE(&lt;expression&gt;, &lt;filter1&gt;, &lt;filter2&gt;, …)</p><p>For e.g. We create a Total Charges measure in Telco Customer Churn dataset. The TotalchargesF is the measure with the filter on Gender = Female. This approach leverages the CALCULATE function to dynamically apply the filter condition on the Gender column, ensuring that only female customers’ total charges are considered in the calculation of the Total Charges measure.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*p6cnv02Qcchiti79No1AIg.png" /><figcaption>DAX: CALCULATE</figcaption></figure><p><strong>2. FILTER</strong></p><p>FILTER function returns a table that contains only the rows that meet specified criteria. It’s commonly used to apply filters within calculations or to create subsets of data.</p><p>Syntax:</p><p>FILTER(&lt;table&gt;, &lt;condition1&gt;, &lt;condition2&gt;, …)</p><p>For e.g If we want filter to a dataset to include only records where the gender is male. By employing the FILTER function and specifying the condition to filter by “male” in the gender column, we effortlessly isolate the desired subset of data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xatxV7ETNSk0a3jwHxY2PQ.png" /><figcaption>DAX: FILTER</figcaption></figure><p><strong>3. RELATED</strong></p><p>The RELATED function in DAX is used to follow relationships between tables in a data model and retrieve a related value from another table based on a defined relationship. It is commonly used in scenarios where you need to fetch values from a related table within a calculated column, measure, or calculated table.</p><p>Syntax:</p><p>RELATED(&lt;column&gt;)</p><p>For e.g we have two tables in our data model: Orders and Products. The Order table contains columns for ProductID, SalesAmount, and Date, while the Products table contains columns for ProductID and ProductName. You want to create a calculated column in the Order table that fetches the ProductName from the Products table based on the ProductID. You can use the RELATED function to achieve this:</p><pre>Product Name = RELATED(Products[ProductName])</pre><p>In this example, the RELATED function fetches the ProductName from the Products table based on the ProductID in the Sales table. This allows you to include the ProductName in the Sales table without denormalizing the data.</p><p><strong>4. SUMX</strong></p><p>The SUMX function in DAX is used to iterate over each row of a table, apply an expression or calculation to each row, and then sum up the results of those calculations. It’s particularly useful when you need to perform calculations across multiple rows or apply calculations to filtered subsets of data.</p><p>Syntax:</p><p>SUMX(&lt;table&gt;, &lt;expression&gt;)</p><p>For e.g we have a Orders dataset with columns for Product, Quantity, and Unit Price. You want to calculate the total sales amount by multiplying the quantity sold by the unit price for each product and then summing up the results. Here we can use SUMX.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L2mYsWEhulKv0H1c7gprMg.png" /><figcaption>DAX: SUMX</figcaption></figure><p>In this example, the SUMX function iterates over each row of the Orders table, calculates the sales amount for each row by multiplying the Quantity column by the Unit Price column, and then sums up the results to get the total sales amount.</p><p><strong>5. AVERAGEX</strong></p><p>AVERAGEX function calculates the average of an expression for each row in a table or a table that results from applying a filter to the table, and then returns the average of these values. It’s useful for calculating averages across multiple rows or applying dynamic filtering.</p><p>Syntax:</p><p>AVERAGEX(&lt;table&gt;, &lt;expression&gt;)</p><p>For e.g In the orders table we have information with columns for Product, Quantity, and Unit Price. If we want to calculate the average sales amount per product, we can use AVERAGEX to compute the average sales amount for each product by multiplying the Quantity and Unit Price for each row and then averaging the results across all rows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hu9OemORk8I1YpFN_kKBpw.png" /></figure><p>In conclusion, mastering the top 5 DAX expressions discussed in this blog — including SUMX, FILTER, CALCULATE, RELATED, and AVERAGEX – empowers data analysts and business professionals to unlock the full potential of Microsoft Excel and Power BI for advanced data analysis and manipulation. These expressions offer versatility and power, enabling users to perform complex calculations, dynamic filtering, context modification, relationship navigation, and dynamic averaging with ease. By incorporating these expressions into their analytical workflows, users can enhance their ability to extract valuable insights, drive informed decision-making, and derive actionable intelligence from their data.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=59b25cf2b0e0" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>