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:
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
Power BI Desktop
Power Pivot for Excel
Supports bi-directional cross filtering security, relationships, direct query and table options.
Supports only single direction relationships and calculated columns only.
Security roles can be defined.
Security roles cannot be defined.
#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:
- Use CALCULATE function to control filter context of measures
- Use ALL to remove the existing filters on date dimension
- Use FILTER to identify which rows of date dimension to use.
- CONTAINS may also be used as an alternative.
Written by Sajin Sahadevan
He is a Microsoft Certified Technology Specialist cum Digital Marketing Expert and has experience of 6 years. He loves learning about latest technologies like CRM, collaboration tools, marketing solutions etc.