"dax commands and tips"

82 Topics

Top 10 DAX Commands and Tips Every Power BI Developer Should Know
Hello Fabric Community! 👋 I've been improving my DAX skills while building Power BI dashboards. Here are 10 essential DAX functions that I use frequently and recommend learning. 1. CALCULATE() Changes the filter context and is the foundation of many advanced measures. Total Sales = CALCULATE(SUM(Sales[Amount])) 2. DIVIDE() Safely performs division and avoids divide-by-zero errors. Profit Margin = DIVIDE([Profit], [Sales], 0) 3. IF() Creates conditional logic. Status = IF([Sales] > 100000, "Target Achieved", "Below Target") 4. SWITCH() A cleaner alternative to multiple nested IF statements. Rating = SWITCH( TRUE(), [Score] >= 90, "Excellent", [Score] >= 75, "Good", [Score] >= 50, "Average", "Needs Improvement" ) 5. RELATED() Retrieves values from related tables. Category = RELATED(Product[Category]) 6. FILTER() Creates custom filter conditions. High Value Sales = CALCULATE( SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] > 1000) ) 7. ALL() Removes filters to calculate totals or percentages. Sales Percentage = DIVIDE( [Total Sales], CALCULATE([Total Sales], ALL(Sales)) ) 8. DISTINCTCOUNT() Counts unique values. Unique Customers = DISTINCTCOUNT(Sales[CustomerID]) 9. RANKX() Ranks products, customers, or regions dynamically. Product Rank = RANKX( ALL(Product[Product Name]), [Total Sales] ) 10. DATEADD() Compares performance across different time periods. Sales Last Year = CALCULATE( [Total Sales], DATEADD('Date'[Date], -1, YEAR) ) Bonus Tips Use VAR to improve readability and performance. Prefer DIVIDE() over the / operator. Build reusable measures instead of calculated columns whenever possible. Keep measure names clear and consistent. Test measures with small datasets before using them in reports. What DAX function do you use the most? Mine is CALCULATE() because it's incredibly powerful for creating dynamic measures. #MicrosoftFabric #PowerBI #DAX #DataAnalytics #BusinessIntelligence #FabricCommunity #PowerBIDeveloper Lijiayi07
Solved
Prince0011
2 months ago Place DAX Commands and Tips
789Views
3likes
3Comments
Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs
🔹 Data Structure I am working with financial data for companies across multiple years (2020–2025). The dataset includes: Company (VAT Number) Financial Year Region Sector Financial metrics: Net Sales EBITDA Total Assets Example (raw wide format – simplified) VAT Year Region Sector Net Sales EBITDA Total Assets A 2020 Attica Retail 500,000 50,000 1,000,000 A 2022 Attica Retail 700,000 80,000 1,200,000 A 2023 Attica Retail 0 0 1,100,000 B 2022 Crete Industry 2,000,000 200,000 3,000,000 B 2023 Crete Industry 3,000,000 300,000 3,500,000 C 2021 Attica Services BLANK BLANK BLANK C 2022 Attica Services 5,000,000 400,000 6,000,000 D 2022 Thessaly Retail 12,000,000 1,000,000 15,000,000 D 2023 Thessaly Retail 13,000,000 1,200,000 16,000,000 E 2020 Crete Services 800,000 60,000 900,000 🔹 Data Transformation I have unpivoted the financial columns, so the data in Power BI looks like this: VAT Year Region Sector Attribute Value A 2022 Attica Retail Net Sales 700,000 A 2022 Attica Retail EBITDA 80,000 A 2022 Attica Retail Total Assets 1,200,000 … … … … … … 🔹 Base Measures Net Sales =CALCULATE(SUM('Page1'[Value]),'Page1'[Attribute] = "Net Sales")EBITDA =CALCULATE(SUM('Page1'[Value]),'Page1'[Attribute] = "EBITDA")Total Assets =CALCULATE(SUM('Page1'[Value]),'Page1'[Attribute] = "Total Assets") 🔹 KPIs Weighted Total Asset Turnover = Net Sales / Total Assets Weighted EBITDA Margin = EBITDA / Net Sales 🎯 REQUIREMENTS I want the report to support three independent filtering mechanisms: 1️⃣ Standard Filtering (WORKS) I can already filter: Financial Year Region Sector All accounts and KPIs respond correctly. 2️⃣ Sales Buckets (WORKS) I implemented dynamic sales segmentation: Step 1 – Bucket definition Sales Bucket =SWITCH(TRUE(),[Net Sales] < 1000000, "<1M",[Net Sales] >= 1000000 && [Net Sales] < 10000000, "1–10M",[Net Sales] > 10000000, ">10M",[Net Sales] <= 10000000, "<=10M") Step 2 – Disconnected table Sales Buckets =DATATABLE("Bucket", STRING,{{"<1M"},{"1–10M"},{">10M"},{"<=10M"}}) Step 3 – Selection logic (multi-select compatible) Selected Bucket = VAR NetSales = [Net Sales] RETURN IF( SUMX( VALUES('Sales Buckets'[Bucket]), SWITCH( TRUE(), 'Sales Buckets'[Bucket] = "<1M" && NetSales < 1000000, 1, 'Sales Buckets'[Bucket] = "1–10M" && NetSales >= 1000000 && NetSales < 10000000, 1, 'Sales Buckets'[Bucket] = ">10M" && NetSales > 10000000, 1, 'Sales Buckets'[Bucket] = "<=10M" && NetSales <= 10000000, 1, 0 ) ) > 0, 1, 0 ) Step 4 – Applied as filter I apply: Selected Bucket = 1 and both sums and KPIs work correctly. 3️⃣ Common Sample (NOT WORKING) This is the main issue. 🎯 Goal I want a slicer that lets the user define a set of years (Sample Years). Then: 👉 A company belongs to the Common Sample if: It has non-zero and non-blank Net Sales EBITDA Total Assets in ALL selected Sample Years ✔ Example If user selects: 👉 Sample Years = {2022, 2023} Then: Company A → ❌ excluded (has 0 in 2023) Company B → ✔ included Company C → ❌ excluded (missing 2021 irrelevant, but 2022 ok → depends only on selected years) Company D → ✔ included Company E → ❌ excluded (no data in selected years) 🔴 Important Requirement Once a company is included in the Common Sample: 👉 We must be able to analyze it across ALL years (e.g. 2020–2025) —not only the selected sample years. ❗ Problem I attempted multiple approaches using: measures (COUNT / FILTER / SUMX) disconnected tables (Sample Years) TREATAS APPLY FILTER logic similar to Sales Buckets However: Results either return all companies or all BLANK values or do not respond to Sample Year slicer or create circular dependency errors when trying calculated tables ❓ QUESTION What is the correct modeling approach in Power BI to implement: 👉 A dynamic common sample filter (based on multiple selected years) that: Filters companies based on validity across selected years Works together with other filters (Year, Region, Sector, Sales Bucket) Still allows analysis across all years Works correctly with aggregated measures and weighted KPIs Any guidance on proper DAX pattern or data modeling approach would be greatly appreciated! 😄
Solved
atziovara
4 months ago Place DAX Commands and Tips
1.5KViews
1like
10Comments
Previous Year Measure for Line Chart
Hello. I am trying to create a YOY cumulative active customer by month line chart. The line should show the number of customers who made at least one purchase. One for Selected Year and another for Selected Year - 1. And, the total is rolling, so the line should be going up over time. Because there is a year filter in play, I am having difficulties writing a measure that provides a PY Active Customer line for the line chart. If I have 2026 selected, previous years' active customers is reduced. So, I have to removefilter the Calendar. However, this causes the PY active customer line ignore the month on the X axis resulting in a flat total line. Please help me write a measure that accurately calculates PY active customers and still works in a line chart. Should look something like this: Sample file here. Thanks.
Solved
etane
5 months ago Place DAX Commands and Tips
3.4KViews
0likes
10Comments
DAX logic needed to determine order lines value of new, existing or shipped orders
Hello! I am trying to come up with the DAX logic to determine order lines value of new, existing or shipped orders. New order value needs to show a sum of USD of new order lines in the most recent week (orders not present in previous week). Existing order value needs to show a sum of USD of order lines that exist in both current and previous weeks (summing only values in current week to avoid duplication). Shipped value is sum of orders that exist in previous week, but not current week. I can’t split table into weeks, no new tables in DAX based on my fact table. I must only operate in DAX, no MS Query solutions. I am not restricted on the number of columns or measures I can create with DAX. I have ‘Calendar’ table in addition to my fact ‘Orders’ table. Here is sample of fact ‘Orders’ table:
Solved
DAX_learner1
8 months ago Place DAX Commands and Tips
656Views
1like
4Comments
Need a Help in Dax Measure
I have a Sales Fact table and a Products dimension table. The requirement is to calculate Total Sales Amount only for products that are currently active. We have an “Active Flag” column in the Product table which is either 1 or 0. Active = 1 means the product is currently active, and Active = 0 means discontinued. The problem is that the report must show historical values correctly. So if a product was sold 2 years ago, the sales amount from that period should still be included, even if the product is discontinued now. But the total value should only add amounts from products which are active according to the filter applied on Product[ActiveFlag]. In simple words: • Use only products where ActiveFlag = 1 • Respect report filters (category, region, date, etc.) • Sales for inactive products should be ignored completely I tried using a filter on the visual, but it removes historical sales even though they should still be counted when ActiveFlag = 1 in the filter context. How do I write a measure that sums Sales Amount only when the product is active but still respects all slicer filters?
Solved
GalrioSamoel
9 months ago Place DAX Commands and Tips
529Views
0likes
2Comments
Inflation Rates Adjusting Year of Payout to Loss Year accounting for inflation
Hello, I have been tasked out with creating a dashboard that shows the payout spend compared to the loss year spend, accounting for inflation (year over year, various years). Basically, I want to see if im spending more money today than the loss date. Data and rates below. Payout Date Loss Date (Year) Payout Year Region Total Inflation Rate Year 1-Jan-24 2023 2024 A -1547.72 2.10% 2017 1-Jan-24 2023 2024 B 4040.44 2.40% 2018 1-Jan-24 2023 2024 C 104.2 1.80% 2019 1-Jan-24 2023 2024 A 12231 1.20% 2020 1-Jan-24 2019 2024 B 2561.4 4.70% 2021 1-Jan-24 2023 2024 A 27135.18 8.00% 2022 1-Jan-24 2022 2024 B -12285.27 4.10% 2023 1-Jan-24 2023 2024 A 315123 0.00% 2024 1-Jan-24 2014 2024 B 407 1-Jan-24 2017 2024 A 200206.6 1-Feb-24 2023 2024 B 749.47 1-Feb-24 2023 2024 C 22837.77 1-Feb-24 2017 2024 A 7 1-Feb-24 2023 2024 B 15403.35 1-Feb-24 2024 2024 C 165 1-Feb-24 2023 2024 A 406.98 1-Feb-24 2022 2024 B 31.35 1-Feb-24 2016 2024 C 1986 1-Feb-24 2023 2024 A 7497.12 1-Feb-24 2023 2024 B 12342.62 1-Feb-24 2015 2024 C 4964.75 1-Apr-23 2014 2023 A 3161.5 1-Apr-23 2017 2023 B 6064.65 1-Apr-23 2019 2023 C 492.27 1-Apr-23 2017 2023 C 2628 1-Apr-23 2023 2023 C 72 1-Apr-23 2023 2023 A 4952.46 1-Apr-23 2015 2023 B 200.48 1-May-23 2023 2023 C 68621.64
Anonymous
10 months ago Place DAX Commands and Tips
695Views
0likes
2Comments
How to calculate percentage of column total?
Hi DAX gurus, I have some good news! AI won't be replacing us anytime soon. Both ChatGPT and CoPilot were unable to solve this. All I am trying to do is write a dynamic DAX measure that calculates the percentage of students who obtained a grade X over the column total. I have many filters/slicers on this page. As you can see in the attached image, the default tooltip for "100% stacked bar chart" gives the right percentage (15.38%, in this case) every time. I can change the filters that affects the total, and I still get the correct percentage. My written DAX measure Grade Percentage fails to do so. I'd like your help in writing the correct DAX measure. I will eventually use this measure to create a customized Tooltip. P.S. - The REPLY has the Data Model too. Many thanks, Sarthak
Solved
sarthakgirdhar
11 months ago Place DAX Commands and Tips
1KViews
0likes
5Comments
Best Practice for Creating Optimized Date Dimension Table (Including Fiscal Year: April–March) Post
Hi all, I'm working on building a Date Dimension table for my Power BI model and would appreciate your suggestions on the best practices and an optimized query to generate it. Here's what I'm looking for in the Date table: Date Day Month Number Month Name Month Order (for sorting) Quarter Quarter Name Year Fiscal Year Fiscal Quarter Week Number Day of Week / Weekday Name 👉 Important Note: Our Fiscal Year starts in April and ends in March. I’m aiming to these for performance and scalability are important, so an optimized approach would be ideal. If anyone has a tried-and-tested query (especially one handling fiscal logic cleanly), or tips on calculated columns/transformations for this, please share. It would be really helpful for me and others facing the same use case. Thanks in advance!
Solved
GanesaMoorthyGM
1 year ago Place DAX Commands and Tips
1.7KViews
0likes
3Comments
How calculate the ‘change’ month to month to drive the Unit Rollforward in dax
Hi Folks, I am connecting from snowflake Datamart to Power BI Using View. I have retriving Created_dt, Year,Month,ID,Name, sum(Units) from the view. It is subscription based business. I want BOP, New Units, Expansion, Contraction,Churn, EOP Not able to understand the below logic. Can you please help in this . Thank in Advance. BOP… # units at the Beginning of a Period + New Units… # units added in the period that were not in the prior period Ie. If a Client was activated in Jun ’25… May ’25 should have 0, and June ’25 should have >0.. New Units + Expansion… # units added between periods with an Existing Customer June ’25 – 100 units May ’25 – 90 units Expansion = 10 units - Contraction… # units reduced between periods with an Existing Customer June ’25 – 80 units May ’25 – 100 units Contraction = (20) units - Churn… # units lost between periods, ending period must be 0 June ’25 – 0 units… this must be Zero to be categorized as Churn May ’25 – 100 units Chrun – (100) units EOP = BOP + New + Expansion – Contraction – Churn DATA: Created_dt Year_Created Month_Created ID Name UNITS 6/14/2022 2022 6 1175 Planet Entertainment 52973 7/26/2022 2022 7 1185 sony Entertainement 5758 9/19/2022 2022 9 204 Amazon Entertainement 27594 10/26/2022 2022 10 1224 Netflix 25692 10/30/2022 2022 10 1228 Z5 0 1/4/2023 2023 1 1282 Aha 22475 3/13/2023 2023 3 1336 JioHotstar 4280 4/3/2023 2023 4 1373 MX Player 0 6/9/2022 2022 6 1172 Voot 26240 8/1/2022 2022 8 1189 Youtube 204104 10/25/2022 2022 10 1221 Eros Now 36778 10/30/2022 2022 10 1227 Alt Balaji 448957 1/19/2023 2023 1 1111 Discovery ++ 232743
Solved
Gopal_PV
1 year ago Place DAX Commands and Tips
626Views
0likes
2Comments
Create a Matrix that has 3,6,12 month averages of measures
This picture showcases the end goal (sorry i dont have a better example) that i am trying to acheive. I do have a calender table and all the measures but cant seem to find how to achieve this for the matrix visual. Please reach out with any knowldege on this!
Solved
lovekheart
1 year ago Place DAX Commands and Tips
1KViews
0likes
5Comments