Power BI Natural language Q&A – Type a question and create a report

Power BI Portal offers an amazing new way of creating reports. You can just type a question like “show total amount by country and by year”. Power BI now analyzes the raw data and creates a visual report instantly. No drag drop required. It needs to be seen to be believed.  Take a look at this video:

This video is part of my new Power BI course on Udemy – Pivot Table to Power BI.  This is my first online course and therefore the learning curve has been very steep.  However, I am just about done with content creation and the course will be launched very shortly.  Stay tuned for further announcements!

How to Eliminate VLOOKUP using Relationships in Power BI

VLOOKUP is one of the most commonly used functions in Excel.  However, it comes with its own set of problems:

  • It increases the file size
  • It consumes lots of processing power, making things slower
  • It is limited to a single table.  This means you cannot use VLOOKUP to search for a value in a different table or sheet
  • It only looks right
  • It can only handle a single criteria
  • If the lookup column contains duplicate values, it will match the first value only

Continue reading How to Eliminate VLOOKUP using Relationships in Power BI

Quick Calc in Power BI

Quick Calc in Power BI (Desktop as well as Portal) is a handy feature. Similar to Show Values As in Pivot Tables.

How to use Quick Calc in Power BI

When you add a measure in the value area, it can be shown as % of Grand Total. Just open the dropdown next to the field and choose Quick Calc.

Quick Calc in Power BI - menu option  Quick Calc in Power BI - Dialog

In the dialog, choose the summarization as desired and then choose Percent of of grand total option from the Show Value As dropdown.  Here is the result. I have added the same column twice. Once to show the actual and other as % of total.


With little bit of tweaking of formatting options in Power BI designer, nice looking reports can be created. One of the most common complaints is that the default font size is too small. That can be changed by going to the General section of formatting area.

Font options in Power Bi

The Show Values As dropdown has only one option as of now. Of course, it is nothing compared to the Pivot Table Show Values As capability – which offers  14 options. I am sure more options will be added to Power BI soon.

Eliminates a measure

Without Quick Calc in Power BI, you would have to create a measure for the % Total with the following syntax.

%ofTotal =
SUM ( Query1[Likes] ) / CALCULATE ( SUM ( Query1[Likes] ), ALLSELECTED () )

Or better still, to avoid DIV/zero error in a single command:

%ofTotalBetter =
    SUM ( Query1[Likes] ),
    CALCULATE ( SUM ( Query1[Likes] ), ALLSELECTED () ),

The base formatting in Power BI itself is rather primitive at this point of time. This code was formatted using the amazing tool created by Alberto Ferrari and Marko Russo called Dax Formatter. You just copy paste valid DAX code there and it formats it automatically. Output can be an image, HTML or Word. API also available.

Power BI Udemy Course coming very soon

Power BI is becoming more and more powerful day by day. It is time everyone got to know about it. It is the next natural progression from Pivot tables. It is not just easy to use. It gives you more capabilities which you have never had before – and that too with lesser effort.

As promised, the detailed Power BI course is almost ready. It will be released in the near future. Watch this place for further announcements.


Office 365 Worst Practices – Part 10 – No Focus on Office

Here is the next installment in the Office 365 Worst Practices series.  Office 365 contains many components. For regular users most of these components are new, except one – Office. Training programs are conducted during the adoption phase to educate users – with No Focus on Office itself. Only server products are covered. Bad idea!

In this post I will explain why this is a major problem and how to solve it. In summary, if you do not know how to use Office well, you will never be able to get maximum gains from Office 365 platform.

Office 365 Worst Practices

Continue reading Office 365 Worst Practices – Part 10 – No Focus on Office

How to conduct simultaneous Remote and Local Training

Traditionally, training is either performed for remote audience or local audience. However, in recent times, I am getting frequent requests to deliver remote and local training simultaneously.

I recently conducted a large training program where we covered 2000+ people in just one day. Only 300 people present in the room, rest on Skype for Business, across 6 countries! I learnt many best practices during this session. Here are the best practices.

Remote and Local Training

Continue reading How to conduct simultaneous Remote and Local Training