Any Good Read on Row Concept & Concept Transition

[jzhao]

Hello, I am an Excel user who recently started using BI. I'm looking for some good resources that explain concept transition in BI.  In Excel, if I want to multiply column 1 with column 2, I just type "col1*col2". However, in Power BI, I need to use the following formula: "calculate(Table1,sumx(col1*col2))". It seams some formula force BI to calculate row by row while other formulas don't. Do variables force row concept in BI?  Can anyone guide me to the right resources or help me with this particular problem?

Table1

col1	col2	result1
1	0.3	0.3
5	0.5	2.5

[123abc · Accepted solution]

t's great that you're transitioning from Excel to Power BI for business intelligence tasks. The concept you're referring to, where calculations are performed row by row, is known as "row context" or "row-level context" in Power BI. Understanding how row context works is crucial for creating DAX (Data Analysis Expressions) formulas in Power BI.

In Power BI, DAX formulas often work on aggregates by default, which means they perform calculations at the aggregated level of the data. However, when you want to perform row-level calculations or context transitions, you need to use functions like SUMX, FILTER, or CALCULATE to explicitly define the desired context for your calculations.

In your example, when you write the formula "calculate(Table1, SUMX(col1 * col2))," you are telling Power BI to perform the multiplication row by row within the specified table (Table1) and then aggregate the results.

To get a better grasp of row context and concept transition in Power BI, I recommend the following resources:

1. Power BI Documentation: The official Power BI documentation is a great place to start. It provides comprehensive information about DAX functions, row context, and context transition. You can find detailed explanations and examples there.

2. Books: Consider reading books on Power BI, such as "The Definitive Guide to DAX" by Marco Russo and Alberto Ferrari or "Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel" by Rob Collie. These books offer in-depth insights into DAX and its concepts.

3. Online Courses: There are many online courses and tutorials that cover Power BI and DAX. Platforms like Udemy, Coursera, and edX offer courses on these topics. Look for courses that focus on DAX and data modeling.

4. Forums and Communities: Participating in Power BI forums like the Microsoft Power BI Community or Stack Overflow can be very helpful. You can ask questions, share your challenges, and learn from the experiences of others.

5. YouTube Tutorials: Many YouTubers and Power BI experts create video tutorials on various aspects of Power BI and DAX. These can be a practical and easy way to learn.

As for your specific question about whether variables force row context in Power BI, the use of variables doesn't necessarily force row context. Variables are used to store intermediate results and make your DAX code more readable. Row context and filter context are determined by the functions and filters applied in your DAX formula.

It's important to understand how DAX functions work with the row context and filter context to achieve the desired results in your calculations. The resources mentioned above will help you gain a deeper understanding of these concepts and how to work with them effectively in Power BI.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.