Power BI Series – Top Questions You Must Ask Yourself – Part II

Hello everyone! Hope you enjoyed reading my previous blog on the Power BI Series. As discussed earlier, I received more Q&A on Power BI and this will help you attain in-depth knowledge about this solution.

For users who haven’t read the first part of the blog, click on the link below:

Power BI Series – Top Questions You Must Ask Yourself – Part I

Read more blogs on Power BI:

• How to get Microsoft Graph API Data on Microsoft Power BI
• How to Leverage Power BI for your SharePoint Reporting

Now, let’s get back to my second set of questions:

#1. Common Table Function for Grouping Data

Use the table function: SUMMARIZE()

The recommended practice is for specifying table & group by columns and not by metrics. Use ADDCOLUMNS functions if required.

#2. Difference in Data Modelling Between Power BI Desktop and Power Pivot for Excel

#3. Usage of FILTER Function

The FILTER function helps in returning a table with condition applied for each of its source table row. It is generally used as a parameter to another function like CALCULATE.

FILTER is an iterator therefore, it can severely affect the performance of larger source tables. However, FILTER parameters can only function on single column at a time. It also cannot reference a metric.

#4. Different Excel BI Add-in

The important Excel BI add-ins are:

• Power Pivot: It’s used for data analysis and modeling.
• Power Map: It displays insights on 3D Map.
• Power Query: Helps to find, edit and load external data.
• Power View: Used for designing visual and interactive reports.

#5. xVelocity In-Memory Analytics Engine – Power Pivot

xVelocity in-memory analytics engine is the main element for Power Pivot. It helps in handling large amount of data as it stores data in column databases and memory analytics – that results in easy and quick data processing as it loads all information to RAM memory.

#6. Benefits of Using Variables in DAX

By evaluating and declaring a variable, it can be used several times in a DAX expression which omits the need of additional queries of source database. Variables are scoped to their query or measure and can’t be shared among queries, measures or defined at model level. Variables can have DAX expressions more logical to interpret.

#7. About Power Pivot

Have you came across this add-in on your Excel 2010 or later versions before? This interesting feature helps you import thousands of rows of data from several data sources into a single Excel spreadsheet. You can create calculated columns using formulas, create relationships between heterogeneous data, build PivotCharts and PivotTables. This will help you to deep dive into data analysis and take business decisions wisely without the need of IT support.

#8. CALCULATE and CALCULATETABLE Functions

These functions allow users to modify filter context of tables or measures. It helps you in overriding filter context from queries, add to existing filter context of queries and removing existing filter context from queries.

#9. Power Pivot Data Model

Power Pivot Data Model is created with data types, columns, tables and table relations. This model is typically made for holding data for business entities.

#10. How to Create Trailing x Month Metrics Using DAX Against Non-standard Calendar

Follow the below steps:

1. Use CALCULATE function to control filter context of measures
2. Use ALL to remove the existing filters on date dimension
3. Use FILTER to identify which rows of date dimension to use.
4. CONTAINS may also be used as an alternative.

E.g. CALCULATE(FILTER(ALL(‘DATE’),……))