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.

image

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 =
DIVIDE (
    SUM ( Query1[Likes] ),
    CALCULATE ( SUM ( Query1[Likes] ), ALLSELECTED () ),
    0
)

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.