81. What is a calculated column in Power BI and why would you use them?
Calculated Columns are DAX expressions that are computed during the model’s processing/refresh process for each row of the given column and can be used like any other column in the model. Calculated columns are not compressed and thus consume more memory and result in reduced query performance. They can also reduce processing/refresh performance if applied on large fact tables and can make a model more difficult to maintain/support given that the calculated column is not present in the source system.
82. What are many-to-many relationships and how can they is addressed in Power BI?
Many to Many relationships involve a bridge or junction table reflecting the combinations of two dimensions (e.g. doctors and patients). Either all possible combinations or those combinations that have occurred.
- Bi-Directional Cross filtering relationships can be used in PBIX.
- CROSSFILTER function can be used in Power Pivot for Excel.
- DAX can be used per metric to check and optionally modify the filter context.
83. Why might you have a table in the model without any relationships to other tables?
There are mainly 2 reasons why we would have tables without relations in our model:
- A disconnected table might be used to present the user with parameter values to be exposed and selected in slicers (e.g. growth assumption.)
- DAX metrics could retrieve this selection and use it with other calculations/metrics.
- A disconnected table may also be used as a placeholder for metrics in the user interface.
- It may not contain any rows of data and its columns could be hidden but all metrics are visible.
84. What is the Power BI Publisher for Excel?
You can use Power BI publisher for Excel to pin ranges, pivot tables and charts to Power BI.
- The user can manage the tiles – refresh them, remove them, in Excel.
- Pinned items must be removed from the dashboard in the service (removing in Excel only deletes the connection).
- The Power BI Publisher for Excel can also be used to connect from Excel to datasets that are hosted in the Power BI Service.
- An Excel pivot table is generated with a connection (ODC file) to the data in Azure.
The Publisher installs all necessary drivers on local machine to establish connectivity.
85. What are the differences between a Power BI Dataset, a Report, and a Dashboard?
Dataset: The source used to create reports and visuals/tiles.
- A data model (local to PBIX or XLSX) or model in an Analysis Services Server
- Data could be inside of model (imported) or a Direct Query connection to a source.
Report: An individual Power BI Desktop file (PBIX) containing one or more report pages.
- Built for deep, interactive analysis experience for a given dataset (filters, formatting).
- Each Report is connected to atleast one dataset
- Each page containing one or more visuals or tiles.
Dashboard: a collection of visuals or tiles from different reports and, optionally, a pinned.
- Built to aggregate primary visuals and metrics from multiple datasets.
86. What are the three Edit Interactions options of a visual tile in Power BI Desktop?
The 3 edit interaction options are Filter, Highlight, and None. Filter: It completely filter a visual/tile based on the filter selection of another visual/tile. Highlight: It highlight only the related elements on the visual/tile, gray out the non-related items. None: It ignores the filter selection from another tile/visual.
87. What are some of the differences in report authoring capabilities between using a live or direct query connection such as to an Analysis Services model, relative to working with a data model local to the Power BI Desktop file?
With a data model local to the PBIX file (or Power Pivot workbook), the author has full control over the queries, the modeling/relationships, the metadata and the metrics. With a live connection to an Analysis Services database (cube) the user cannot create new metrics, import new data, change the formatting of the metrics, etc. – the user can only use the visualization, analytics, and formatting available on the report canvas. With a direct query model in Power BI to SQL Server, for example, the author has access to the same features (and limitations) available to SSAS Direct Query mode.
- Only one data source (one database on one server) may be used, certain DAX functions are not optimized, and the user cannot use Query Editor Functions that cannot be translated into SQL statements.
88. How does SSRS integrate with Power BI?
Below are some of the ways through which SSRS can be integrated with Power BI:
- Certain SSRS Report items such as charts can be pinned to Power BI dashboards.
- Clicking the tile in Power BI dashboards will bring the user to the SSRS report.
- A subscription is created to keep the dashboard tile refreshed.
- Power BI reports will soon be able to be published to SSRS portal
89. What formats is Power BI available in?
- Power BI desktop—for desktop computers
- Power BI service—an online SaaS (software as a service)
- Mobile Power BI apps—for iOS and Android devices
All of these can be used in conjunction. For example, you might create a report on your desktop, and then publish and share it online so that colleagues on mobile devices can read it.
90. Describe the building blocks of Power BI.
- Visualizations. Visualization is a chart, graph or similar visual representation of data.
- Datasets. A dataset is the group of data used to create visualization, such as a column of sales figures. Datasets can be combined and filtered from different sources using built-in connectors.
- Reports. A report is a group of visualizations on one or more pages; for example, charts, graphs, and maps can be combined to create a report.
- Dashboards. A dashboard lets you share a one-page visualization with others, who can then interact with your dashboard.
- Tiles. A tile is visualization on your dashboard or in your report. As the creator, you can move tiles around.
91. What are the main components of the Power BI toolkit, and what do they do?
- Power Query: lets you discover, access and consolidate info from different sources
- Power Pivot: a modeling tool
- Power View: a presentation tool for creating charts, tables and more
- Power Map: lets you create geospatial representations of your data
- Power Q&A: lets you use natural language to get answers to questions; for example, “What were the total sales last week?”
92. What is a content pack, and why would you use one?
A content pack is a ready-made, predefined collection of visualizations and reports using your chosen service (for example, Salesforce). You’d use one when you want to get up and running quickly, instead of creating a report from scratch.
93. What are the three fundamental concepts of DAX?
Syntax. This is how the formula is written—that is, the elements that comprise it. The Syntax includes functions such as SUM (used when you want to add figures). If the syntax isn’t correct, you’ll get an error message.
Functions. These are formulas that use specific values (also known as arguments) in a certain order to perform a calculation, similar to the functions in Excel. The categories of functions are date/time, time intelligence, information, logical, mathematical, statistical, text, parent/child and other.
Context. There are two types: row context and filter context. Whenever a formula has a function that applies filters to identify a single row in a table, row context comes into play. When one or more filters are applied in a calculation that determines a result or value, filter context comes into play.
94. Why and how would you use a custom visual file?
You’d use a custom visual file if the pre-packaged files don’t fit the needs of your business. Custom visual files are created by developers, and you can import them and use them in the same way as you would the pre-packaged files.
95. What are some of the most common sources for data in the Get Data menu?
Excel, Power BI datasets, web, text, SQL server and analysis services.
96. What are the categories of data types?
- All
- File
- Database
- Power BI
- Azure
- Online Services
- Other
97. Name some commonly used tasks in the Query Editor.
- Connect to data
- Shape and combine data
- Group rows
- Pivot columns
- Create custom columns
- Query formulas
98. What is grouping, and how would you use it?
Power BI Desktop groups the data in your visuals into chunks. You can, however, define your own groups and bins. For grouping, use Ctrl + click to select multiple elements in the visual. Right-click one of those elements and, from the menu that appears, choose Group. In the Groups window, you can create new groups or modify existing ones.
99. Describe responsive slicers.
On a report page, you can resize a responsive slicer to different sizes and shapes, and the data contained in it will be rearranged to match. If a visual becomes too small to be useful, an icon representing the visual takes its place, saving space on the report page.
100. In Power Query, what is query folding?
This is when steps defined in the Query Editor are translated into SQL and executed by the source database, instead of by your own device. It helps with scalability and efficient processing.
