Category: Hints & Tips

Hints and tips for MS Office and other applications. Contributions from our team of talented trainers.

Posted on

Power BI’s Mysterious Calculate Function: 1 – Row Context

DAX (Data Analysis eXpressions) is the function language in Power BI desktop. When DAX is used to create measures, it can hard to understand the logic sometimes. Especially one function, the Calculate function, can be challenging.

data

This is the first part of a series of blog posts, which will investigate the mysterious Calculate function.

The Calculate function reacts differently to row, column, filter, and table context to other DAX functions. We need it for nesting a number of DAX functions.

Examples of how the Calculate function works in Row Context

The examples will be based on sales data, and you can see the data model below.

data

 

In the first example the Customers need to be categorised by number of orders. Customers with more than 40 orders are A client and the rest are B clients.

In the example below an IF function has been used to manage the logic in a column calculation in the Customers table. The DAX looks like this:

Customer A and B clients = If(COUNTROWS(Orders)>40,”A Client”,”B Client”)

The logic is that every order has a unique record in the Orders table, but as you can see below, the DAX return that all the customers are A Clients. The DAX does not understand that each customer needs to be tested for more than 40 orders.

data

To get a better understanding of this the DAX has been changed in the example below to only calculate the number of orders (rows in the orders table).

The example shows 6037 for each customer. The result is not filtered by the relationship and do not display the number of orders for each customer. So in the IF logic example, you just need to test whether 6037 is greater than 40.

Communicating to Power BI about the data test

To communicate to Power BI that we want to test the number of orders for each row in the customers table (return the number of orders for each customer), the calculated column needs to be changed to: # of Orders = COUNTROWS(relatedtable(Orders))

The RelatedTable function will filter the number of orders by each customer and as you can see in the example below, the column will now show the result by customer, and not just the total number of orders.

To get the first example right the DAX also needs to be amended to:

Customer A and B clients = If(COUNTROWS(RELATEDTABLE( Orders))>40,”A Client”,”B Client”)

You can see in the result below, that now the DAX return the right categories.

So the examples show that when you do calculated columns, you will need the RelatedTable function to get the result for each row, but not if you use the mysterious Calculate function. The Calculate function is working with the row context completely different than the rest of the DAX functions.

In the example below the DAX has been changed to:

Customer A and B clients = calculate(If(COUNTROWS( Orders)>40,”A Client”,”B Client”))

The IF logic has been nested in a calculate function and the RelatedTable function had been removed. You can see that the amended calculation returns the right result.

Conclusion

I call the Calculate function the mother of DAX functions. It is the most important DAX function (my personal opinion), but to get the most out of it you will need to understand how the function reacts to row, column, filter, and table context.

In the next blog post in this series, you will see how the mysterious Calculate function is different from other DAX functions when it comes to filter context.

Posted on

Business Writing: Tips for Efficient Communication

You can improve your profitability by ensuring clear and concise communication. Business writing is present at every level of any organisation and assumes many forms.

In this blog, learn 5 key tips for business writing, as well as a checklist of considerations.

 Business Writing – what is it?

Business writing is any type of writing that is used in a professional setting. It is a purposeful piece of writing that conveys relevant information to the reader. Consequently, it must be presented well. Examples of business writing include client proposals, reports, memos, emails, and notices. As such, good business writing skills improve internal as well as external communications.

Proficiency in business writing is a critical aspect of effective communication in the workplace. Poor business writing wastes time. Ultimately, this can lead to costly mistakes.

By focusing on the following five areas, you can create professional documents that are quick and easy to read and action. In doing so, you can improve your company’s efficiency.

5 Tips

1  Know the purpose of your message

The broad field of business writing can be broken down into the following four categories based on the objective.

Instructional

When your aim is to give the reader information that will help them complete a task, it’s called Instructional writing. To make the information easy to follow and instructive, you could offer a step-by-step format. This business writing style is often written with a neutral, competent tone. For example, manuals and technical use Instructional writing techniques.

Informational

Informational business writing comprises of documents essential to the core functions of the business for tracking growth, outlining plans, and complying with legal obligations. For example, the financial statements of a company, minutes of a meeting, or report writing.  Informational writing needs to be accurate. Therefore, you should consider the best way to present detail carefully. Charts and graphs can be read far more quickly than tables of figures.

 Persuasive

Persuasive writing uses words to impress the reader and convince them to listen or to act. We associate sales and marketing with persuasive writing. It includes proposals and press releases. You must be able to attract the reader quickly and focus on what’s in it for them.

Transactional

Transactional writing is writing that is part of a chain of communication. Most of our day-to-day communication at the workplace falls under the transactional business writing category. The bulk of such communication is by email, but also includes official letters, forms, and invoices. The writing style is usually brief and to the point.

For all four categories of writing, your introduction should answer these three questions from the perspective of the reader:

What is this?

Why am I getting it?

What do you want me to do?

 

2 Be clear and concise

People often misinterpret unclear and long-winded messages. Presenting the crux of the passage in the first 150 words is important. It saves the reader time and sharpens the argument.

Business executives value a document they can scan quickly. Using numbered or bulleted lists, clear headings, concise paragraphs, and the use of bold formatting to highlight keywords will enable them to do this.

 

3 Use the active voice

The active voice puts the subject or person before the action.

Active voice is a way to take control of your writing. When you sound confident, your reader or audience is more likely to trust what you have to say. Passive voice often makes the writer appear unsure. The active voice is direct, strong, and easier to read. There is a clear difference between ‘The report was submitted by Bob’ (passive) and ‘Bob submitted the report’ (active).

 

If you want to present a solid argument and influence your reader, use the active voice in your business writing.

 

4 Templates

Using templates for regular types of communication such as proposals or reports can speed up the process. Templates will also help you to create a consistent brand style. However, don’t rely on them to the point of losing originality or becoming dull and ineffective. Ensure that emails are read. Your email is not the only one which the recipient will read that day!

 

5 The Final Check

Correct grammar, spelling and punctuation speeds up the ability to read a document. In addition, the tone of the phrases you use can completely alter the message you want to deliver. Read your document through a couple of times before sending and if necessary, get a second opinion.

 

Considerations: Remember the 10 ‘Cs’ of Business Writing

  1. Complete: include the when, where, why, who, and how
  2. Concise: remove redundant words and stock phrases
  3. Clear: make sure there’s no ambiguity or doubt
  4. Conversational: write like you’re talking to someone face-to-face
  5. Correct: use the correct spelling, grammar, and information
  6. Coherent: write down your thoughts and ideas in a logical manner
  7. Credible: always check your facts and your sources
  8. Concrete: avoid using vague words and phrases such as idioms
  9. Courteous: use a positive tone and put the reader first
  10. Considerate: make your writing easy to read and scan

 

For further hints and tips, have a look at our Essential Business Writing Skills You Need to Learn piece!