When building measures in Power BI using DAX, two commonly used aggregation functions are COUNT and COUNTA. While they sound similar, they serve different purposes and choosing the right one can prevent inaccurate results in your reports.
COUNT: Counting Numeric Values Only
The COUNT function counts the number of non-blank numeric values in a column.
DAX syntax:
COUNT ( Table[Column] )
Key characteristics of COUNT”:
Works only on numeric columns
Ignores blanks
Ignores text values entirely
When to use COUNT:
You want to count numeric entries such as:
Number of transactions
Number of invoices
Number of scores, quantities, or measurements
The column is guaranteed to contain numeric data
Example: If Sales[OrderAmount] contains numbers and blanks, COUNT(Sales[OrderAmount]) returns the number of rows with a valid numeric amount.
COUNTA: Counting Any Non-Blank Values
The COUNTA function counts the number of non-blank values of any data type, including text, numbers, dates, and Boolean values.
DAX syntax:
COUNTA ( Table[Column] )
Key characteristics of “COUNTA”:
Works on any column type
Counts text, numbers, dates, and TRUE/FALSE
Ignores blanks only
When to use COUNTA:
You want to count:
Rows where a column has any value
Text-based identifiers (e.g., Order IDs, Customer Names)
Dates or status fields
You are effectively counting populated rows
Example: If Customers[CustomerName] is a text column, COUNTA(Customers[CustomerName]) returns the number of customers with a non-blank name.
COUNT vs. COUNTA: Quick Comparison
Function
Counts
Ignores
Typical Use Case
COUNT
Numeric values only
Blanks and text
Counting numeric facts
COUNTA
Any non-blank value
Blanks only
Counting populated rows
Common Pitfall to Avoid
Using COUNTA on a numeric column can produce misleading results if the column contains zeros or unexpected values. Remember:
Zero (0) is counted by both COUNT and COUNTA
Blank is counted by neither
If you are specifically interested in numeric measurements, COUNT is usually the safer and clearer choice.
In Summary
Use COUNT when the column represents numeric data and you want to count valid numbers.
Use COUNTA when you want to count rows where something exists, regardless of data type.
Understanding this distinction ensures your DAX measures remain accurate, meaningful, and easy to interpret.
Excel Power Query is a powerful, no-code/low-code tool that allows you to combine and transform data from multiple sources in a repeatable and refreshable way. One common use case is merging two Excel files or worksheets based on multiple matching columns, similar to a SQL join. Power Query is a major part of Power BI, but it can be used in Excel.
When to Use Power Query for Merging
Power Query is ideal when:
You receive recurring Excel files with the same structure
You need a reliable, refreshable merge process
You want to avoid complex formulas like VLOOKUP or XLOOKUP across many columns
Step-by-Step Overview
1. Load Both Data Sources into Power Query
Open Excel and go to Data → Get Data
Choose From Workbook (for separate files) or From Table/Range (for sheets in the same file)
Tip: Ensure the columns you plan to merge on have the same data types (e.g., text vs. number).
Load each dataset into Power Query as a separate query
2. Start the Merge Operation
In Power Query, select the primary table
Go to Query → Merge Queries
Choose the secondary table from the dropdown
3. Select Multiple Matching Columns
Click the first matching column in the primary table
Hold Ctrl (or Cmd on Mac) and select additional matching columns
Repeat the same column selections in the secondary table, in the same order
For example, if you needed to perform the merge on CustomerID, OrderDate, and Region, you would click Customer ID, then hold the Ctrl key and click OrderDate, then (while still holding down the Ctrl key) click Region.
Power Query treats this as a composite key, and all selected columns must match for rows from both tables to merge.
4. Choose the Join Type
Select the appropriate join kind:
Left Outer – Keep all rows from the first table (most common) and brings in the values for the matching rows from the second table
Inner – Keep only matching rows from both tables
Full Outer – Keep all rows from both tables, merging the table where there is a match and having just the values from the respective tables when there is no match
Click OK to complete the merge.
5. Expand the Merged Data
A new column appears containing nested tables
Click the expand icon to select which columns to bring in
Remove unnecessary columns to keep the dataset clean
6. Load and Refresh
Click Close & Load
The merged dataset is now available in Excel
When source files change, simply click Refresh to update everything automatically
Key Benefits
Handles multi-column joins cleanly and reliably
Eliminates fragile lookup formulas
Fully refreshable and auditable
Scales well as data volume grows
In Summary
Using Power Query to merge Excel data on multiple columns brings database-style joins into Excel, making your workflows more robust, maintainable, and professional. Once set up, it saves time and reduces errors—especially for recurring reporting and analytics tasks.
The location of your source files has changed, and now you need to update your Power BI report to use the new location. To update the directory or location of your source file, in Power BI Desktop, click Transform Data -> Data Source Settings
Then click on the entry that corresponds to the path you need to update.
Update or entirely change the path and click ok. Apply your changes.
It becomes a little more complicated when you are changing a local folder to a SharePoint location, which we will cover in another post, but for changing location of single files, it’s that simple.
You may run into this error when loading Power BI:
"load was cancelled by error in loading a previous table"
If you do get this error, keep scrolling down to see what the “inducing” error is. This message is an indication that there was an error previous to getting to the current table in the process. The real, initial error will be more descriptive. Start with resolving that error(s), and then this one will go away.
I was getting this error message when I attempted to refresh a Power BI application:
"Column 'Date' in table 'Date Dim' contains blank values and this is not allowed for columns on the one-side of a many-to-one relationship or for columns that are used as the primary key of a table"
However, despite what the message indicated, I double-checked and confirmed that I did not have any blank values in the ‘Date Dim’ table.
It turns out that you may also get this error (although incorrectly worded in my opinion) if the blanks are in the joining table. In my case, I had blanks in a ‘Snapshot Date’ column in the fact table that was joined to the ‘Date Dim’ table. Once these blanks were filled, the refresh ran without error.
One thing to look out for in these cases (since this is what happened in my case), if your source is Excel, undo all filters to make sure that you do not have any rows being filtered out when checking for blanks values across your columns, because this could potentially inadvertently hide the rows with the blank values and cause you to miss them.
Data visualization is at the heart of analytics. Choosing the right chart or visual can make the difference between insights that are clear and actionable, and insights that remain hidden. There are many visualization types available for showcasing your data, and choosing the right ones for your use cases is important. Below, we’ll walk through some common scenarios and share information on the charts best suited for them, and will also touch on some Power BI–specific visuals you should know about.
1. Showing Trends Over Time
When to use: To track how a measure changes over days, months, or years.
Best charts:
Line Chart: The classic choice for time series data. Best when you want to show continuous change. In Power BI, the line chart visual can also be used for forecasting trends.
Area Chart: Like a line chart but emphasizes volume under the curve—great for cumulative values or when you want to highlight magnitude.
Sparklines (Power BI): Miniature line charts embedded in tables or matrices. Ideal for giving quick context without taking up space.
2. Comparing Categories
When to use: To compare values across distinct groups (e.g., sales by region, revenue by product).
Best charts:
Column Chart: Vertical bars for category comparisons. Good when categories are on the horizontal axis.
Bar Chart: Horizontal bars—useful when category names are long or when ranking items. Is usually a better choice than the column chart when there are many values.
Stacked Column/Bar Chart: Show category totals and subcategories in one view. Works for proportional breakdowns, but can get hard to compare across categories.
3. Understanding Relationships
When to use: To see whether two measures are related (e.g., advertising spend vs. sales revenue).
Best charts:
Scatter Chart: Plots data points across two axes. Useful for correlation analysis. Add a third variable with bubble size or color to generate more insights. This chart can also be useful for identifying anomalies/outliers in the data.
Line & Scatter Combination: Power BI lets you overlay a line for trend direction while keeping the scatter points.
Line & Bar/Column Chart Combination: Power BI offers some of these combination charts also to allow you to relate your comparison measures to your trend measures.
4. Highlighting Key Metrics
Sometimes you don’t need a chart—you just want a single number to stand out. These types of visuals are great for high-level executive dashboards, or for the summary page of dashboards in general.
Best visuals in Power BI:
Card Visual: Displays one value clearly, like Total Sales.
KPI Visual: Adds target context and status indicator (e.g., actual vs. goal).
Gauge Visual: Circular representation of progress toward a goal—best for showing percentages or progress to target. For example, Performance Rating score shown on the scale of the goal.
5. Distribution Analysis
When to use: To see how data is spread across categories or ranges.
Best charts:
Column/Bar Chart with bins: Useful for creating histograms in Power BI.
Box-and-Whisker Chart (custom visual): Shows median, quartiles, and outliers.
Pie/Donut Charts: While often overused, they can be effective for showing composition when categories are few (ideally 3–5). For example, show the number and percentage of employees in each department.
6. Spotting Problem Areas
When to use: To identify anomalies or areas needing attention across a large dataset.
Best charts:
Heatmap: A table where color intensity represents value magnitude. Excellent for finding hot spots or gaps. This can be implemented in Power BI by using a Matrix visual with conditional formatting in Power BI.
Treemap: Breaks data into rectangles sized by value—helpful for hierarchical comparisons and for easily identifying the major components of the whole.
7. Detail-Level Exploration
When to use: To dive into raw data while keeping formatting and hierarchy.
Best visuals:
Table: Shows granular row-level data. Best for detail reporting.
Matrix: Adds pivot-table–like functionality with rows, columns, and drill-down. Often combined with conditional formatting and sparklines for added insight.
8. Part-to-Whole Analysis
When to use: To see how individual parts contribute to a total.
Best charts:
Stacked Charts: Show both totals and category breakdowns.
100% Stacked Charts: Normalize totals so comparisons are by percentage share.
Treemap: Visualizes hierarchical data contributions in space-efficient blocks.
Quick Reference: Which Chart to Use?
Scenario
Best Visuals
Tracking trends, forecasting trends
Line, Area, Sparklines
Comparing categories
Column, Bar, Stacked
Showing relationships
Scatter, Line + Scatter, Line + Column/Bar
Highlighting metrics
Card, KPI, Gauge
Analyzing distributions
Histogram (columns with bins), Box & Whisker, Pie/Donut (for few categories)
The below graphic shows the visualization types available in Power BI. You can also import additional visuals by clicking the “3-dots” (get more visuals) at the bottom of the visualization icons.
Summary
Power BI, and other BI/analytics tools, offers a rich set of visuals, each designed to represent data in a way that suits a specific set of analytical needs. The key is to match the chart type with the story you want the data to tell. Whether you’re showing a simple KPI, uncovering trends, or surfacing problem areas, choosing the right chart ensures your insights are clear, actionable, and impactful. In addition, based on your scenario, it can also be beneficial to get feedback from the user population on what other visuals they might find useful or what other ways they would they like to see the data.
Thanks for reading! And good luck on your data journey!
Microsoft Fabric is a central platform for data and analytics, and one of its powerful features that supports it being an all-in-one platform is Shortcuts. Shortcuts provide a simple way to unify data across multiple locations without duplicating or moving it. This is a big deal because it saves a LOT of time and effort that is usually involved in moving data around.
What Are Shortcuts?
Shortcuts are references (or “pointers”) to data that resides in another storage location. Instead of copying the data into Fabric, a shortcut lets you access and query it as if it were stored locally.
This is especially valuable in today’s data landscape, where data often spans OneLake, Azure Data Lake Storage (ADLS), Amazon S3, or other environments.
Types of Shortcuts
There are 2 types of shortcuts: table shortcuts and file shortcuts
Table Shortcuts
Point to existing tables in other Fabric workspaces or external sources.
Allow you to query and analyze the table without physically moving it.
File Shortcuts
Point to files (e.g., Parquet, CSV, Delta Lake) stored in OneLake or other supported storage systems.
Useful for scenarios where files are your system of record, but you want to use them in Fabric experiences like Power BI, Data Engineering, or Data Science.
Benefits of Shortcuts
Shortcuts is a really useful feature, and here are some of its benefits:
No Data Duplication: Saves storage costs and avoids data sprawl.
Single Source of Truth: Data stays in its original location while being usable across Fabric.
Speed and Efficiency: Query and analyze external data in place, without lengthy ETL processes.
Flexibility: Works across different storage platforms and Fabric workspaces.
How and Where Shortcuts Can Be Created
In OneLake: You can create shortcuts directly in OneLake to link to data from ADLS Gen2, Amazon S3, or other OneLake workspaces.
In Fabric Experiences: Whether working in Data Engineering, Data Science, Real-Time Analytics, or Power BI, shortcuts can be created in lakehouses or KQL (Kusto Query Language) databases, and you can use them directly as data in OneLake. Any Fabric service will be able to use them without copying data from the data source.
In Workspaces: Shortcuts make it possible to connect across lakehouses stored in different workspaces, breaking down silos within an organization. The shortcuts can be generated from a lakehouse, warehouse, or KQL database.
Note that warehouses do not support the creation of shortcuts. However, you can query data stored within other warehouses and lakehouses.
How Shortcuts Can Be Used
Cross-Workspace Data Access: Analysts can query data in another team’s workspace without requesting a copy.
Data Virtualization: Data scientists can work with files stored in ADLS without having to move them into Fabric.
BI and Reporting: Power BI models can use shortcuts to reference external files or tables, enabling consistent reporting without duplication.
ETL Simplification: Instead of moving raw files into Fabric, engineers can create shortcuts and build transformations directly on the source.
Common Scenarios
A finance team wants to build Power BI reports on data stored by the operations team without moving the data.
A data scientist needs access to parquet files in Amazon S3 but prefers to analyze them within Fabric.
A company with multiple Fabric workspaces wants to centralize access to shared reference data (like customer or product master data) without replication.
In summary: Microsoft Fabric Shortcuts simplify data access across locations and workspaces. Whether table-based or file-based, they allow organizations to unify data without duplication, streamline analytics, and improve collaboration.
You may at times need to create a date value in Power BI either using DAX or M, or in Excel. This is a quick post that describes how to create a date value in Power BI DAX, Power Query M language, and in Excel. Working with dates is an every-day thing for anyone that works with data.
In Power BI DAX, the syntax is:
DATE(<year>, <month>, <day>) //the parameters must be valid numbers
DATE(2025, 8, 23)//returns August 23, 2025
In Power Query M, the syntax is:
#date(<year>, <month>, <day>)//the parameters must be valid numbers
#date(2022, 3, 6) //returns March 6, 2022
In Excel, the syntax is:
DATE(<year>, <month>, <day>)//the parameters must be valid numbers
DATE(1989, 12, 3)//produces 12/3/1989 (officially returns a number that represents the date in Excel date-time code)
Microsoft Fabric OneLake Catalog is the next generation, enhanced version of the OneLake Data Hub. It provides a complete solution in a central location for team members (data engineers, data scientists, analysts, business team members, and other stakeholders) to browse, manage, and govern all their data from a single, intuitive location. It provides an intuitive and efficient user interface and truly simplifies and transforms the way we can manage, explore, and utilize content in Fabric. Usage is contextual and it has unified all Fabric item types (including Power BI items) and expanded support to all Fabric item types, integrating experiences, and providing detailed views of data subitems. It is a great tool.
Why use OneLake Catalog?
This tool will make your work within Fabric easier, and it will reduce duplication of items due to improved discoverability, and it will enhance our ability to govern data objects within the platform. So, check out the resources below to learn more.
We were working with some HR data which included multiple date fields such as Hire Date, Transfer Date, Promotion Date, and Termination Date. We needed to determine the most recent event date before termination. We ended up using the MAXX function to do this.
Sample dataset to demonstrate the scenario:
Using the following dataset to demonstrate the example:
Employee
Hire Date
Transfer Date
Promotion Date
Termination Date
Alice
2020-01-15
2021-05-10
2022-03-20
2023-06-15
Bob
2019-11-01
2020-07-15
2021-10-05
(blank)
Carol
2021-03-25
(blank)
2021-09-14
2022-02-28
The goal is to calculate the most recent event and event date (i.e., the latest event and its date) between Hire Date, Transfer Date, and Promotion Date for each row. Termination Date was excluded from the comparison because the goal was to find the latest event before Termination (if that had occurred).
Using MAXX for Row-by-Row Evaluation
MAXX is an iterator function in DAX, meaning it evaluates an expression for each row of a table, then returns the maximum value. Iterator functions such as MAXX and SUMX work row-by-row over a table, in contrast to aggregate functions like MAX and SUM which operate over an entire column at once.
Aggregate example (MAX): Finds the highest value in a column across all rows.
Iterator example (MAXX): Evaluates an expression for each row in a virtual table, then finds the highest value.
This makes MAXX ideal for scenarios like this where the various dates are in multiple columns of the same row, and we need to find the max of these dates on each row.
DAX Code Example: This is an example of the code that was used to derive the latest event date.
Create a virtual table with one column and three rows—one for each date we want to consider.
MAXX iterates through this virtual table, evaluates [Value] (the date), and returns the latest / maximum (max) date for each iteration.
Expected Output based on the sample dataset:
Employee
Hire Date
Transfer Date
Promotion Date
Termination Date
Latest Event Date
Alice
2020-01-15
2021-05-10
2022-03-20
2023-06-15
2022-03-20
Bob
2019-11-01
2020-07-15
2021-10-05
(blank)
2021-10-05
Carol
2021-03-25
(blank)
2021-09-14
2022-02-28
2021-09-14
This is much cleaner than using nested IF checks to determine the latest date / latest event for each record. Of course, the MAXX function can be used in other scenarios where you want to find the max value across multiple columns on each row.
Thanks for reading and I hope you found this useful!
Information and resources for the data professionals' community
The Data Community 