close

Calculated Column & Measures in Power BI: Understand the Difference

Power BI is a powerful data visualization application that enables analysts to perform data analysis using bespoke computations. Calculated Columns and Measures are the most common types of calculations. Both are written in DAX (Data Analysis Expressions), Power BI Desktop’s primary formula, query language and make most of the part of Power BI calculations. Let’s […]

Measure vs Calculated Column in Power BI

Power BI is a powerful data visualization application that enables analysts to perform data analysis using bespoke computations. Calculated Columns and Measures are the most common types of calculations. Both are written in DAX (Data Analysis Expressions), Power BI Desktop’s primary formula, query language and make most of the part of Power BI calculations. Let’s check out how calculated column & measure works in Power BI.

 

What is a Calculated Column in Power BI?

A calculated column is a table extension that is assessed for each row in the table. Calculated columns, just like all the other data imported from a data source, are stored in Power BI’s xVelocity in-memory storage. With one exception, a calculated column is nearly identical to a non-calculated column. DAX formulas and values from other columns are used to determine their values.

Calculated columns are only evaluated when you first define them and during a dataset refresh because they exist at the same level as your tables. Deriving the per-unit profit of a product by deducting the cost of the product from the product’s price is an instance of a calculated column.
 

What are the highlights of the Calculated Column?

  • Calculation by row: Row Context
  • Retained in memory (consumes RAM)
  • Computed at the time the report is refreshed (either scheduled basis or manual)

 

Understanding Calculated Columns: A Wider Aspect

Calculated columns can be extracted from the original data source or produced using a DAX expression in Power BI. The best way to think of columns is as static row-level properties.

Example: Profit as a calculated column

Think of a table, which contains sales and expense data. In such a table, profit would be calculated by subtracting costs from sales for each row. As a result, this is essentially a calculated column.

Expression:  Profit = Fact Internet Sales [Sales Amount] – Fact Internet Sales [Total Product Cost]
 

Row by Row Calculation: Row Context

One of the most crucial things to remember about the calculations you perform in the Calculated Column (in the majority of cases, but not always) is that they are performed one row at a time or row by row.
 

Stored in Memory

Calculated Column, like any other column, keeps values in memory. The calculation takes place during Refresh time, and the result is saved in memory.

This means that the more computed memory you have, the more memory you will consume and the longer your refresh time will be. Nevertheless, because many calculations are simple, your refresh time may not be significantly impacted.
 

Calculated Columns: Rules to Consider

When you need to filter your data model using the outcomes. In the Pivot Table, you can’t utilize a Measure on Rows, Columns, Filters or Slicers. If you wish to do this, you’ll need a Column rather than a Measure. Rather than using a Calculated Column, it is better to have the column introduced to your data source.

  • You can reuse the source column in other workbooks without having to rewrite the calculated column if you load it from the source.
  • Import compression has the potential to be improved.
  • Your worksheet will refresh more quickly.

However, if importing from the source isn’t possible, a Calculated Column is your best buddy.

  1. When you’re improving a Lookup (Dimension) table: Since lookup tables are typically smaller (with fewer rows), the negative effects of Calculated Columns aren’t as severe.
  2. When the cardinality of the Calculated Column findings is low: In Power Pivot, the uniqueness of values in a column is the enemy of compression. So, if your Calculated Column returns two possible unique values (e.g., Yes or No) and this column assists you in designing a sophisticated Measure, the Calculated Column is fine. Low cardinality calculated columns compress extremely well.
  3. When you have a complex formula: A complex formula that is stressful on your report to the point where the inadequate run time performance of a measure calculation makes it advisable to “pre-calculate” the result and save it in a column. Although it is doubtful that a self-taught Excel user will write such a sophisticated formula in the early stages of learning Power Pivot, this is an acceptable use case, nonetheless.

With the aforementioned criteria & use cases in mind, you should be able to figure out when to utilize calculated columns & when not to.
 

Problems of Calculated Columns

  1. For each and every row in the table, the results of your formula are calculated and recorded. This consumes disc space as well as RAM, making your report less performant. It probably doesn’t matter if your table has 100 rows. It can be a major issue if your table has 100 million rows.
  2. Calculated column compression may not be as excellent as imported column compression. As a result, Power Pivot does not prioritize Calculated Columns in the same way that it does imported columns.
  3. When the worksheet is updated against the data source, the Calculated Columns are recalculated. This will make the refresh process take longer.

 

What is Measure?

A measure is typically a calculation performed at the aggregated level. This aggregation can be as basic as adding up sales or as complicated as computing monthly average sales over a rolling 12-month period. Measures are dynamic, affecting a subset of data from one or more tables. As a result, the subset of data can be altered using the Power BI Report’s filters, and the calculation will have to be reviewed dynamically. As a result, Measures are not pre-calculated; instead, they will be calculated as needed when they are included in the report.

According to radcad.com – “In fact, 70% of your time when you write DAX is used for writing measures, if not more!”

 

What are the highlights of Measures?

  • Calculated using all of the filters: Contextual Filtering.
  • Isn’t saved and isn’t calculated ahead of time.
  • When you put it on a report page and alter a slicer, filter, or tap on a column chart or any other visual to highlight and it affects this measure’s value, it’s computed on the go.
  • Calculation uses the CPU.

 

Understanding Measure in Power BI: A Wider Aspect

In brackets, measures are referred to by their names (e.g. [Measure Name]). The best way to think of measures is as dynamic aggregations. The level at which they are computed will ultimately determine their worth. They are table-agnostic and give developers a wide range of analytical options.
 

Sum of Sales

The majority of the times, measures are aggregations. A sum of sales is a relatively simple aggregate that we may use as an example.
Aggregating can be accomplished using a variety of DAX functions, including Sum, SumX, Average, Calculate and a plethora of other aggregation functions. Let us now address the most crucial question:
 

How to see the Value of the Measure?

Measurements are made on the fly. In reality, this is one of the most fundamental distinctions between a measure and a computed column. So, if measure values are calculated on the fly, how can you know what they are? The answer is to include it in a report.
 

Measures: The RAM and CPU Outlook

The calculation of the measure is done on the go. This signifies that the value of the measurement is not saved in the memory. Memory or RAM will not be used in any way by the measure. Measures, on the other hand, use the CPU because their calculations must be completed at the time of visualization. If you modify a filter or slicer, you’ll need to recalculate. Because the reaction time must be quick, this calculation is performed by the CPU.
 

Power BI Measures vs Calculated Columns: Which one is better?

When To Use a Power BI Measure Vs A Column in Power Bi?

The primary distinction: The key distinction amongst calculated columns and measures is that columns are evaluated at each row, whereas measures are only examined at the level of granularity in which they are shown. Columns are recalculated at each visual contact, whereas measures are recalculated at each data refresh. This means that, unlike measures, column values are not responsive to filter selection or parameters.
 

Power BI Calculated Column vs Measure Advantages

Here are some of the advantages of utilizing Measures rather than Calculated Columns.

  1. Your workbooks will be smaller and quicker to complete. You are not “materializing” and saving the results of every conceivable calculation in your table in memory and on disc (like with a Calculated Column). Measures are instead calculated on the fly in memory on order, and Measure calculations are typically lightning quick. Only the results that have to be displayed inside the Pivot Table are materialized.
  2. Before the calculations are performed, Pivot Tables filter your data model. This reduces the number of calculations necessary by requiring Measures to be calculated only across the filtered subset of data.

 

Disadvantages of using Measures

Slowed Performance: Table and Matrix Visuals

A measure is calculated on the fly, using the CPU’s processing power as well as temporary memory, so it’s understandable that it would take some time to complete the computation. Nevertheless, you may first notice a performance issue with Table and Matrix. Why?! The fact is that in the filter context, measures are calculated based on a set of values.

It is only calculated once in Card Visual when the calculation occurs. When it occurs in a column or bar chart, it only occurs for a few elements. When using a table, particularly a matrix graphic, you want to show the details, so you add a field with a lot of values. As a result, you wind up with thousands, if not millions, of calculations, which slows down your report.

The precise context of fields in these visuals causes thousands, if not millions, of calculations when you put the measure calculation in a matrix or table visual. Please note that this would still be extremely quick for simple calculations or calculations performed in a properly constructed model. Even with a large number of records and columns. When the measure calculation does not work properly on its own, the problem arises.
 

Power BI Summation of Measures vs. Columns

 
Measure vs Calculated Column

 

Conclusion

To conclude, In power bi, the choice of when to utilize a measure vs. a column is a little more complicated, as measures are aggregations calculated during each visual interaction, whereas columns are row-level expressions assessed during data load and refresh.As a result, measures should be taken into use for Ratios and aggregations (Profit Margins, Percentage of Variation), Expressions that are responsive to the context of a specified filter. Columns are more appropriate for Static and row-level expressions, attributes, and categories.
 

Frequently Asked Questions

  • How do you convert a measure to a calculated column in Power BI?

Step 1:  Pick the measure column that you want to convert into a calculated column (do not tick the checkbox to include the column in the sheet; only select the column).

Step 2: In the “Default Summarization” selection, any aggregation will be applied by default.

Step 3: From the drop-down menu, choose “Do Not Summarize.”
 

  • What is the use of a calculated column in Power BI?

Users can use calculated columns to add new data to an existing table in your model. Rather than querying and loading values into your new column from a data source, users can specify the column’s values using a Data Analysis Expressions (DAX) formula.
 

  • What happens when you use an aggregation function in a calculated column?

Users can create aggregations in a calculated column that use the current row context to obtain related rows from another table, then sum, count, or average the values in the linked rows.
 

  • Can a measure be a table in power bi?

A measures table is essentially a placeholder table that serves as a storage location for your measures. It serves as a container for our measurements rather than containing any actual data.

Power BI vs SSRS: Key Differences You Should Know

Both Power BI & SSRS are Microsoft BI tools with more than dozens of functionality & benefits. We often get overwhelmed about which tool to choose when it comes to Power BI vs SSRS for reporting. Let’s find the basic differences & benefits of each of them.   What is Power BI? Power BI is […]

Power BI vs SSRS

Both Power BI & SSRS are Microsoft BI tools with more than dozens of functionality & benefits. We often get overwhelmed about which tool to choose when it comes to Power BI vs SSRS for reporting. Let’s find the basic differences & benefits of each of them.
 

What is Power BI?

Power BI is a collection of software services, applications, and connectors that operate collectively to convert disparate data sources into rational, visually immersive, and engaging insights. Your data could be in the form of an Excel spreadsheet or a set of hybrid data warehouses that are both cloud-based as well as on-premises.

Power BI makes it simple to connect to your data sources, visualize and uncover what matters, and share your findings with whomever you choose.
 

What is SSRS?

SQL Service Reporting Service (SSRS) is a report-generating platform that runs on a server. SSRS is primarily used for enterprise data visualization, similar to Power BI but with a more traditional approach. It can be used to create, examine, and share reports.

SSRS has a programming interface as well as a collection of processing components that are all integrated. Since SSRS has fewer graphical, drag-and-drop features and more programming-based tools for report creation, it necessitates more manual labor.

It’s like a Microsoft product, and it’s part of the SQL Server suite. It works in tandem with other programs such as Visual Studio and SQL formatting tools.
 

Why & When We need SSRS

Why SSRS?

The following are some of the most compelling reasons to use the SSRS tool:
•  As compared to Crystal Reports, SSRS is a more powerful tool.
•  Reports on relational and multidimensional data are processed faster.
•  Provides users with a better and more reliable decision-making system.
•  Allows users to engage with data without the need for IT assistance.
•  It connects to the World Wide Web for the purpose of distributing reports. As a result, reports are accessible over the internet.
•  SSRS allows you to export reports in a variety of formats. Email can be used to transmit SSRS reports.
•  SSRS has a number of security capabilities that allow you to manage who has access to which reports.
 

When do we need SSRS?

If you only need to generate and use simple, paginated reports on-premises in an enterprise, SSRS is the way to go. In that regard, SSRS is also cost-effective.
 

Types of Reports SSRS Generates

Parameterized reports: A parameterized report finalizes report or data processing by using input values. You can alter the output of a report using a parameterized report by adjusting the values that are specified when the report runs. Drillthrough reports, linked reports, and subreports are all common uses for parameterized reports, which connect and filter reports with relevant data.

Linked reports: A linked report is a report server item that offers a link to a previously published report. It’s akin to a program shortcut that you use to start a program or open a file in terms of concept.

Snapshot Reports: A report snapshot is a report that provides layout information and query results acquired at a certain point in time. Report snapshots are analyzed on a schedule and afterward saved to a report server, unlike on-demand reports, which obtain up-to-date query results as you select the report. The report server gets the stored report from the report server database and displays the data and design that were relevant for the report at the time the snapshot was generated when you choose a report snapshot for viewing.
 

Report Snapshots serve three main purposes

Historical Data: You may create a history of the report that displays how data changes over time by taking a series of report snapshots.

Consistency: Whenever you want to give consistent results for different users who should work with the same data sets, use report snapshots. An on-demand report can offer diverse findings from one minute to the next when dealing with variable data. A report snapshot, on the other hand, enables you to compare data from the same moment in time to data from those other reports or analytical tools.

Performance: You can save time by arranging large reports to execute during off-peak hours & offer other benefits as below. 

  1. Cached reports: A cached report is a copy of a processed report that has been saved. Cached reports increase speed by lowering the number of processing requests sent to the report processor and the time it takes to obtain large reports. They must expire after a certain amount of time, which is commonly measured in minutes.
  2. Clickthrough reports: When you click the interactive data within a model-based report, a clickthrough report displays relevant data from a report model. The report server generates these reports using the information given in the report model. When a clickthrough report is viewed, the person who designed the model selects which fields are interactive and which fields are returned. The report creation tools do not allow you to update certain field settings.
  3. Drilldown reports: Drill-down reports hide intricacy at first and allow the user to toggle selectively hidden report components to tailor the amount of detailed data they view. Drilldown reports are required to obtain all data that can be displayed in the report. 
  4. Drillthrough reports: Drill through reports are ordinary reports that can be viewed via a hyperlink in the original report’s text box. Drillthrough reports are used in conjunction with main reports and are the target of a drill through action for a report item like placeholder text or a chart. The main report provides summary data in a matrix or graphic, for example. Drillthrough links to reports based on the aggregation in the main report are provided by actions defined in the matrix or chart.
  5. Subreports: A subreport is a report that presents a different report within the main report’s body. A subreport is akin to a frame in a Web page in terms of concept. It’s used to put a report inside another report. A sub report can be created from any report. Various data sources can be used in the subreport than in the main report. The report that the subreport displays are normally kept in the very same folder as the parent report on a report server. The parent report can be configured to transmit parameters to the subreport.

As per the Mssqltips – “The number of available data sources available in Power BI surpasses the number of available connections in SSRS, likely by a 5 to 1 ratio” 

 

What are the Components Of SSRS?

Components of SSRS

 

Use the SSRS APIs in bespoke apps to combine or enhance data and report processing. This architecture is mostly made up of the components and tools listed below:

  1. Report Builder: This component allows for easy report design and drag-and-drop capability. Report Builder is a client-side end-user report publishing application that runs on a client computer.
  2. Report Designer: You may use the Report Designer tool to create both simple and complicated reports. Report Designer is a publishing tool that may be found in either BIDS or Visual Studio.
  3. Report Manager: This program makes it simple to access web-based reports. The Report Manager’s default URL is http://server>/reports
  4. Report server: This is a server that stores metadata using the SQL Server database engine.
  5. Report server Database: It holds metadata, resources, report definitions, security settings, delivery data, and so on.
  6. Data sources: Data is retrieved from data sources such as relational and multidimensional data sources by reporting services.

 

Why & When We need Power BI?

1. A fast start: With a simple setup, no training necessary, and dashboards for services like Salesforce, Google Analytics, and Microsoft Dynamics, you’ll be able to receive insights immediately.
2. A more efficient publication and dissemination process: Analysts upload visualizations to the Power BI service rather than emailing or putting them on a shared disc, and their data is updated anytime the underlying dataset is changed.
3. Information that is updated in real-time: As data is sent or streamed in, dashboards refresh in real-time, allowing viewers to instantly solve problems and uncover possibilities. Real-time data and visuals can be displayed and updated in any report or dashboard. Factory sensors, social media sources, and anything else that can gather or send time-sensitive data can be used as streaming data sources.
4. Customize the Power BI app’s navigation: Report developers can adjust navigation to help viewers discover content quickly and comprehend the relations between various reports and dashboards using the “app navigation experiences” feature.
5. Customizable security features: Report developers can use row-level security (RLS) access filters to guarantee that users only see information that is pertinent to them, reducing the chance of people viewing data they shouldn’t.
6. Cortana integration: Power BI integrates with Cortana, Microsoft’s digital assistant. Users can obtain charts and graphs by asking inquiries in plain language. This is especially beneficial for people who utilize mobile devices.
7. Artificial Intelligence: Image recognition and text analytics are available in Power BI, as well as the ability to develop machine learning models and interact with Azure Machine Learning.
 

When to Use Power BI?

Power BI is a great option when:
• Report creation may be readily taught to all users.
• There is a lack of coding expertise.
• It’s necessary to create interactive reports
 

Power BI Paginated Reports vs SSRS reports: Key Differences

SSRS vs Power BI
 

Benefits of Power BI over SSRS

  • Unlike SSRS, Power BI is available for free. We must obtain a SQL Server license in order to use SSRS. However, in order to use PBIRS, we must get a Power BI premium license.
  • Power BI is more graphical and current, whereas SSRS takes longer and requires more manual effort to analyze data and generate reports.
  • PBIRS can be thought of as the more feature-rich version of SSRS.
  • Power BI is coupled with Cortana for an AI-based experience, however, SSRS does not have this feature.

 

Paginated report in SSRS vs Paginated report in Power BI

Power BI paginated reports are designed to be printed or converted to PDF. They also enable you to create layouts that are highly formatted and pixel perfect. Paginated reports are therefore appropriate for operational reports such as sales invoicing.

Consider utilizing a paginated Power BI report when:

  • You’re aware that the report must be printed or saved as a PDF file.
  • Data grid layouts have the potential to expand and overflow. Consider that a table, or matrix, in a Power BI report can’t automatically resize to show all data; instead, scroll bars are provided. When printed, however, there will be no way to scroll to disclose any material that is out of view.
  • Paginated features and capabilities of Power BI work in your favor.

 

Create, distribute, and manage paginated and mobile reports.

The SSRS solution allows you to give the appropriate information to the right people at the right time. The reports can be seen in a computer browser, on a mobile device, or via email.
 

SQL Server Reporting Services now includes the following new features:

  • “Traditional” paginated reports have been upgraded, allowing you to produce modern-looking reports with updated tools and capabilities.
  • New mobile reports with a responsive layout that adapts to various devices and how they are held.
  • A modern web gateway that can be accessed using any modern browser. You can organize and display mobile and paginated Reporting Services reports and KPIs on the new portal. Excel workbooks can also be stored on the portal.

 

Migrating from SSRS to Power BI in short

The report server is configured in two ways for SSRS:

  • Native Mode
  • SharePoint Integrated Mode

We can move reports from On-Premises SSRS to the Power BI report server based on the existing SSRS mode. The standard steps for transitioning from SSRS to Power BI are as follows:

  • Make that the database, application, and configuration files are all backed up.
  • Make a copy of the encryption key.
  • Clone the database on your report server where your reports are stored.
  • Set up the Power BI Report server. You can install the PBI RS Server on the same server as the SSRS instance if you’re utilizing the same hardware.
  • Connect to the cloned database and configure the report server using the Report server Configuration Manager.
  • Clean up the SSRS (Native mode) instance as necessary.

Migrating from SSRS (SharePoint-integrated mode) to Power BI Report server is more difficult than switching from a native mode. This migration will undoubtedly provide some difficulties.
 

Challenges in Replacing SSRS with Power BI

ON-PREMISEs VS. CLOUD

Power BI is a cloud-based solution, whereas SSRS is a server-based solution. This has a significant impact on how data is kept and disseminated. As a result, Power BI can leverage data from a variety of cloud sources. SSRS, on the other hand, is limited to data stored on on-premises servers.

You must obtain a SQL Server license, specify requirements and scope, customize/create your software to meet the requirements, deploy, develop reports in a coding interface, schedule data refreshes, and so on in order to use SSRS. Although Power BI is free, PBIRS requires the purchase of a Power BI Premium license.
 

Replacing the server

  • It’s fine to migrate SSRS 2012/2014/2016 to Power BI. However, the PBI RS server has certain problems while migrating from SSRS 2017. The SSRS configuration manager failed to run a script that refreshes the SSRS 2017 ReportServer database, allowing it to interact with Power BI objects. To fix this, open SQL Compare after the migration, compare the schema of the SSRS 2017 database with the schema of a brand new ReportServer database created by the PBI RS server, and push the missing objects into the SSRS database. For the service to work, it must be restarted.
  • Specific SharePoint report server content must be moved to the PBI RS Server. PBI RS Server to be installed someplace in the environment to accomplish this.
  • Tools like rs.exe should be used to copy report server content from the SharePoint environment to Power BI Report server.

 

Reports in SSRS and Microsoft Power BI

  • SSRS is a traditional reporting and analysis system that requires manual work and time. Power BI, on the other hand, is more graphical and current. To create reports, Power BI includes a graphical interface with drag-and-drop functionality. Working with unstructured data, modern rendering, publishing, integrating, and collaborating across platforms is simple.
  • For simple SSRS reports, moving the queries to Power BI and reproducing the same graphics is simple (tables, charts, etc.). Power BI places a greater emphasis on interactivity and data exploration than SSRS. You can edit reports as you convert them to use features like slicers, cross-filtering, and drilldowns.
  • On the other side, SSRS has a plethora of fine-tuning tools that Power BI simply lacks (including an entire expression language). You can accomplish a lot of things using SSRS that you couldn’t do as easily with Power BI.
  • PBIRS (Power BI Report server) is the successor to SSRS and has greater functionality.
  • Cortana has been incorporated into Power BI for AI-based natural language, Q&A about your data, and reports. This feature is not available in SSRS.
  • You can still use the standard data connections if you’re utilizing SSRS/Paginated Reports. You can only use Analysis Services with Power BI reports.
  • In order for the object to refresh, credentials must be stored in the report. The pinning procedure really creates an SSRS subscription that executes only with stored credentials. Furthermore, if the item name is changed, the object will no longer update based on the subscription. Furthermore, if the pinned object is erased, the associated subscription is not removed automatically and must be done so manually.
  • Finally, an error occurs if you attempt to pin an object while the SQL Server Agent is not operating. To pin objects, the SQL Server Agent must be executed in the same place as the SSRS database.

 

Conclusion

The decision between the two:- Power BI versus SSRS – will be made in the end based on organizational needs. SSRS is a classic tool, but Power BI is cutting-edge. As time goes on, Microsoft’s focus may shift, and SSRS may become a part of Power BI reporting capabilities. However, SSRS has its own customer base, which will continue to grow in the future. Reporting services are improving thanks to technologies like Power BI and SSRS, and end-users from all walks of life are reaping the benefits. After all, successful business intelligence relies heavily on data visualization.

Power BI License Types: A Comparative Study to Choose the Best License for Your Enterprise

When it comes to power bi license types, there are three types of licenses: free, pro, and premium. The location of the content, how they will interact with it, and whether or not the content leverages Premium features all influence the sort of license a user needs.   Types of Licensing in Power BI There […]

Power BI License Type

When it comes to power bi license types, there are three types of licenses: free, pro, and premium. The location of the content, how they will interact with it, and whether or not the content leverages Premium features all influence the sort of license a user needs.
 

Types of Licensing in Power BI

There are three Power BI license types:

Power BI Pro

To process content, a Power BI license with a free or Pro per-user license only uses a shared and limited capacity. Users with a Power bi Pro license can only collaborate with other Power BI Pro users if the content is stored in that shared capacity. They may consume user-generated content, publish material to app workspaces, share dashboards, and subscribe to dashboards and reports. Pro users can share content with users who don’t have a Power BI Pro subscription while workspaces are at Premium capacity.
 
The Power BI Pro version, which is the full version of Power BI, allows you to use Power BI to generate reports and dashboards with limitless viewing, sharing, and use. Kindly refer to below comparison to understand licensing better. 
 
Free vs Pro License Type  Power BI
Img source: Microsoft.com
 
Furthermore, customers with a Pro license can share dashboards, reports, and data with a large number of other Pro license holders. They can also set up an app-based workspace with a storage restriction of 10 GB per user.
 

Power BI Premium

The Premium capacity-based license in Power BI lets users with a free license act on content in Premium capacity workspaces. A user with a free license can only use the Power BI service to connect to data and create dashboards and reports in My Workspace outside of Premium capacity. They are unable to exchange content or publish it to other workspaces.
 
Unlike the Desktop and Pro licenses, Power BI premium gave users in an organization a designated unit capacity. The Premium Workspace can save up to 50 GB of data in the cloud and has a total storage capacity of 100 TB.
Instead of depending on shared capacity, BI Embedded allows enterprises to employ their own hardware and dedicated capacity.
 

Power BI Premium Per User (PPU) License

Content developed by a Premium Per User licensed user can only be accessed with other Premium Per User licensed users unless it is specifically placed on a workspace hosted on a Premium capacity when utilizing Premium Per User licensing.
 

Power BI Capacity-Based License Options

License type Capabilities when a workspace is in shared capacity Additional capabilities when a workspace is in Premium capacity
Power BI (free) Content in My Workspace is accessible. Consume content that has been shared with them.
Power BI Pro Share material with other workspaces, share dashboards, subscribe to dashboards and reports, and publish with Pro license users. Distribute material to users who have unrestricted access to it.
Power BI premium Per User Share material with users who have a Premium Per User license, share dashboards, subscribe to dashboards and reports, and share with users who have a Premium Per User license. Users with both free and Pro licenses should be able to access the material.

 
Power BI premium Per User Share material with users who have a Premium Per User license, share dashboards, subscribe to dashboards and reports, and share with users who have a Premium Per User license. Users with both free and Pro licenses should be able to access the material.
 

Power BI premium (P SKU)

Power BI Premium is aimed at businesses who want a comprehensive BI solution that gives them a single view of their company, partners, customers, and suppliers.
Power BI Premium is a subscription-based service that lets consumers consume content via mobile apps, internally built apps, or the Power BI online (Power BI service). As a result, Power BI Premium can handle both internal and external customer-facing applications.
 

Embedding Power BI as part of Power BI Premium – Microsoft Office offering

P and EM are the two SKUs available in Power BI Premium
• P SKUs (P1-P5) for embedding and enterprise functionality, with a monthly or annual commitment and a license to deploy Power BI report server on-premises.
• Organizational embedding SKUs (EM1-EM3), which require an annual commitment and are payable monthly. Only volume licensing arrangements are available for the EM1 and EM2 SKUs. You won’t be able to buy them directly.
 

Power BI Embedding (EM SKU & A SKU)

  • ISVs and developers who wish to integrate visuals into their applications should use Power BI Embedded.
  • Users can consume content saved on Power BI Embedded capacity through applications that use Power BI Embedded.
  • Power BI Embedded comes with a product code (SKU).

 

Find out Which Power BI License Type You Have

To use the Azure portal to view and assign Power BI licenses, follow these steps:

  1. Go to the Azure portal and log in.
  2. Look for Azure Active Directory and choose it.
  3. Select Licenses from the Azure Active Directory resource menu under Manage.
  4. From the resource menu, choose All products, then choose a Power BI license types to see a list of licensed users.
  5. Select + Assign from the command bar to assign a license. Choose a user on the Assign license screen, then click Assignment options to enable a Power BI license for that user account.
  6. Choose the checkbox next to the user’s name, and after that select Remove license to remove the license.

 

Content Creation, Content Publication and Content Consumption Capabilities in A Different Version of Power BI

Types of Licensing in Power BI
 

Power BI Desktop vs Power BI Pro vs Power BI Premium

Power BI Desktop Power BI Pro Power BI Premium
Content Creation Prepare data and create infinite visualizations with Office 365 Enterprise E5; included in all Office 365 Enterprise E5 subscriptions. Data from the service can be analyzed in Excel, and reports can be up to 1GB in size. Enable dashboard and report wrapping in an app, which may then be shared with users who have a free license.
Content Publication Upload the report to the internet. Users with a Pro license can publish content to other workspaces, exchange content with other Pro license users, and share dashboards. The ability to make public reports across the enterprise without having each user have their own license.
Content Consumption Use 10GB of cloud storage to store Power BI reports and Excel workbooks. Set up email subscriptions and collaborate on reports and dashboards using collaborative workspaces. Storage, data refresh, and compute capability have all improved dramatically.

 

Pros & Cons of Power BI Desktop, Power BI Pro and Power BI Premium

Power BI Desktop Power BI Pro Power BI Premium

Pros: Self-service data analysis is available for free. Connect to over 70 data sources, both on-premises and in the cloud. Data transformation and modeling Make charts and graphs. Use the Power BI service to share reports. Make visual collections for dashboards and reporting.

Pros: Make peer-to-peer sharing possible. Make a workspace with other Power BI users, you may share reports, dashboards, and data. Integrate with other Microsoft services, such as Azure data services. Pros: Provide space and capacity in a Microsoft-hosted cloud. Allow users to choose how to use the designated capacity.

Allow recipients to publish reports without requiring them to have a license. Deliver more scale and performance than the shared capacity.

Access to the most up-to-date embedded analytics features and a consistent set of skills.

Cons: Does not enable viewing & sharing of reports and dashboards with other BI users. Does not allow Power BI to leverage Office 365 E5 Groups.
There are no app workspaces, API embedding, or email subscriptions available.
Cons: There is no pagination for printing.

Cons: Viewing is limited to reports and dashboards

Does not permit the creation and distribution of apps.

 

Publishing and Consuming Reports on the Power BI Service

1. Select Publish on the ribbon or File > Publish >Publish to Power BI in Power BI Desktop.
2. If you haven’t previously done so, sign into Power BI.
3. Decide on a destination. You can find the workspace into which you wish to publish by searching your list of possible workspaces. You can use the search box to narrow down your workspaces. To publish, select the workspace and thereafter click the Select button.
You will receive a link to your report after it has been published. To open the report on your Power BI site, click the link.
 

Publishing and Consuming Reports On-Prem with Power BI report Server

1. Make a report: With Power BI Desktop, you can create stunning reports. Use a freeform drag-and-drop canvas and current data visualizations to visually examine data.
2. Upload the report to the Power BI report server: Directly publish reports to Power BI report server. Arrange your reports into folders, control access, and make changes as needed.
3. Disseminate and consume content across several devices: To address a variety of business needs, share reports for your consumers to consume on the web and on mobile devices.
 

Power BI Pricing: How much does Power BI License Cost?

An organization can take advantage of the following features with the free Power BI version:
• Creating data visualizations.
• Reports from the authors.
• 1 GB of storage space.
• Finally, data streaming at up to 10K rows per hour is possible.
 

Cost Structure of Power BI Pro

Power BI Pro is available for $9.99 per month per user. When businesses choose this license, they get the following benefits:
• Cloud-based modern BI and self-service.
• Sharing, publishing, cooperation, and ad hoc analysis are the next steps. MS is in charge of everything.
 

Cost Structure of Power BI premium

With an annual subscription, the Power BI premium costs $4,999 per month per storage resource and dedicated cloud compute. When businesses choose this license, they get the following benefits:
• Enterprise BI, big data analytics, and on-premises reporting.
• Advanced administrative and deployment controls.
• Dedicated cloud computing and storage resources.
• Make use of Power BI content.

As per Power BI community – “For 22 users the capacity pricing options would most likely is not a viable option and instead you should look to license all users on a per license basis”.

 

Conclusion

The Power BI cost and license you choose will be determined by the size of your company and its consumption demands, such as how many people need to access or evaluate reports, how many people need a dashboard or to create reports, and how many people need an at-a-glance reporting style. So, take some time to evaluate each tier and see which one best suit your company.

Row Level Security in Power BI: Setup Process & Concept

  What is Row Level Security in Power BI? In Power BI, row-level security (RLS) can be utilized to limit data access for certain users. One can set filters within roles to limit access to data at the row level. Members of a workspace have accessibility to datasets in the workspace in the Power BI […]

Row Level Security Power BI

 

What is Row Level Security in Power BI?

In Power BI, row-level security (RLS) can be utilized to limit data access for certain users. One can set filters within roles to limit access to data at the row level. Members of a workspace have accessibility to datasets in the workspace in the Power BI service. This data is not restricted by RLS.
RLS can be configured for data models loaded into Power BI Desktop by users. It can also be configured on datasets that use DirectQuery, such as SQL Server.
 

Why Row-level security is important?

Row-Level Security (RLS) is significant because it makes security in your application easier to design and code. RLS aids in the implementation of data row access limits.
 

What is Dynamic row-level security in Power BI?

One of the most effective and practical methods to restrict data views throughout an organization is by using dynamic row-level security (DRLS). DRLS filters the dataset via DAX functions depending on the Power BI service user’s log-in credentials.
 

Pre-requisite for Row-Level Security?

To create Row-Level Security, we must execute each of the following steps one at a time :-
1. Make a list of users who will have access to the information. According to their login context, these users will be permitted access to some of the data.
2. Develop an integrated table-valued function in SQL as the following step. The filter criteria for the table on which RLS is to be deployed will be stored in this function.
3. Establish a security policy for the table and apply the above integrated table-valued function to it as the final stage.
 

How to Implement Row-Level Security in Power BI Desktop

Create Role Model
 

  • Create a DirectQuery connection or upload the data into your Power BI Desktop report.
  • Go to the Modeling tab at the top and choose Manage roles to create row-level security.
  • Select Create from the Manage roles dialogue box that displays.
  • After clicking on create, a list of tables from the data model will appear, from which you may choose a filter to implement row-level security.
  • You’ll notice that a new sample DAX expression will be added.
  • Substitute the values in the DAX expression.
  • Finally, make a role for a manager who wishes to keep an eye on the statistics.

Within the Power BI Desktop, you can set roles and rules. When you publish to Power BI, the role definitions are also published. It’s a key part of Power BI’s Row Level Security. Follow these procedures to define security roles:
 
Note: For Analysis Services live connections, you can’t define roles in Power BI Desktop. This must be done using the Analysis Services model.
 

Validate the Roles within Power BI Desktop

View Role Model
 
After you’ve developed your roles, run them using Power BI Desktop to see how they perform.
1. Choose View as Roles from the drop-down menu.
2. Choose a role you created and click OK to apply it. The data is rendered appropriate for that role in the report.
3. You can alternatively choose another user and provide a specific user. Because the Power BI service and Power BI Report Server use the User Principal Name(UPN), it’s best to provide it.
4. Click OK, and the report is generated depending on the user’s view.
Other users typically show different results in Power BI Desktop if you’re utilizing dynamic security based upon your DAX expressions.
 

What’s Good About Row-Level Security in Power BI Desktop?

Row-level security refers to the application of protection to individual data rows. For example, a developer wants to review the Sales & Population data for Country Finland should only access data for Finland, not from any other Country.
The reason for this was because Row Level Security was not included in the Power BI model. The security setting is now part of the model in the latest version of Power BI Desktop and will be delivered with the model.
 

Limitations Row-Level Security in Power BI Desktop?

• You must recreate norms and rules in Power BI Desktop if you already specified them in the Power BI service.
• RLS can only be applied to datasets prepared using Power BI Desktop. If you wish to use RLS with Excel datasets, you’ll need to convert them to Power BI Desktop (PBIX) files beforehand.
• ETL and DirectQuery connections are the only ones that work. The on-premises approach handles live connections to Analysis Services.
• At the moment, RLS does not support Cortana.
 

Implement Row-Level Security in Power BI Service

• Go to the report you just published and open it.
• After that, go to Datasets and click on the settings button for the report dataset you just published to start using RLS.
• From the drop-down option, choose Security.
• The Row-Level Security page appears, displaying all three roles established on the desktop.
• Select Test as Role from the right-click menu of a role’s options.
 

Testing Row-Level Security in the Power BI Service

• After heading to the workspace where your file was published, locate the dataset and pick Security from the options menu, as row-level security is controlled at the dataset level.
• On the following screen, you will see the role you established in Power BI Desktop, as well as a field to fill in your role membership, or the users or groups who are members of this role.
• When you drag the mouse over the role and obtain the ellipsis, you’ll see the option to test row-level security. When you click on this ellipsis, you’ll have the option to ‘test as role.
• This will lead you to the report page, wherein row-level security appears to be applied right away.
• You can change the user you are testing by selecting the menu at the top and entering a different email address.
 

Things to Remember Beforehand

• After a PBI file is published to the service, various factors such as workspace membership roles, data set permissions, and data set security role memberships can affect whether row-level security functions as intended. 
• To grant access to a viewer, return to the row-level security page, pick the role, and input the viewer’s email address in the Members field, and after that click Add and Save. 
• When generating role membership, the ideal practice is to simply utilize security groups; it’s a lot easier to manage than directly adding names.
• To see if this succeeded, go back to your report page and enter the name of the viewer you just introduced, and the page should sort to what the view can now see.
 

What is Object-Level Security?

Model authors can use object-level security (OLS) to hide individual tables or fields from report viewers. The table or column doesn’t really exist from the perspective of the viewer. You can limit access to sensitive object names as well as data with object-level security.
 

Advantages & Limitations of Object-level Security

Advantages:
The names and information of objects are also protected to prevent a hostile user from finding that they exist. This new layer of security stops people with insufficient access levels from accessing business-critical or sensitive personal data.
Using tools that support the XMLA API, you can create OLS rules in the Power BI dataset.
Limitations:

  • If a model breaks a relationship chain, table-level security cannot be set for it. At design time, an error is produced. Table Y, for example, cannot be secured because of linkages between tables X and Y and Y and Z. A query on table X cannot traverse the linkages between tables X and Y, or Y and Z if table Y is guarded. In this situation, a distinct relationship between tables X and Z might be set up.
  • It is not possible to mix row-level and object-level security from distinct roles since this could result in inadvertent access to secured data. Users who are members of this kind of combination of roles receive an error at query time.
  • If a dynamic calculation refers to a secured table or column, it is subsequently restricted. While there is no way to expressly secure a measure, you can secure it implicitly by changing the phrase to refer to a protected database or column.
  • Relationships referencing a secured column work as long as the table in which the column is found is not secured.

 

How do I Restrict Data Access in Power BI?

With Power BI Desktop, you can utilize row-level security (RLS) to limit data access for certain users. Filters limit data on a row-by-row basis. Within roles, you may define filters. With Power BI Desktop, you can now specify RLS for data models imported into Power BI.
 
Conclusion
Row-level security is among the most essential security features in both the desktop and cloud versions of Power BI, as we’ve seen above. Users from the Azure Active Directory can be mapped to the roles and given secured access to the reports in addition to the email addresses that we mapped the users to. It’s worth noting, though, that any person with permission to edit the report and dataset will also be able to see the underlying data for other roles.

Power BI Import Mode vs Direct Query

When connecting to data in Power BI Desktop, one of the first things each Power BI developer needs to know is whether to click the Import or Direct Query box. Before choosing on which strategy is ideal for you, you need to think about a few things. This decision can be made based on the […]

When connecting to data in Power BI Desktop, one of the first things each Power BI developer needs to know is whether to click the Import or Direct Query box. Before choosing on which strategy is ideal for you, you need to think about a few things. This decision can be made based on the report, but your company should have a strategy in place for how to employ both of these ways. Let’s go over each connection method one by one and find out what is better when it comes to Power BI Import vs Direct Query.
 

What is Direct Query and Import Mode in Power BI?

Get Data SQL Source in Power BI
Option to Select Import & Direct Query within Power BI
 
Direct Query – When you connect a data source using the Direct Query technique, your dashboard will query the data source immediately during runtime. Each filter and interaction with the report will result in a series of new queries. Since no data is imported into Power BI, the user can always query the data that already exists in the data source.

Each & every single request goes straight to the data source (which is more often SQL database) and pulls the data from there into Power BI.

Import — Using the Import mode of connection, Power BI will cache the data you’re linked to, providing a snapshot of your data at a specific point in time. All of your data’s interactions and filters will be applied to this compressed cache source rather than the original data source.
 

Is Direct Query faster than Import Mode in Power BI?

Import mode is the best option if your data is less than 1 GB and isn’t constantly updating. Because all data comes from the Power BI Desktop Cache. So in such scenario, an import mode is faster than Direct Query mode when the data file size is below 1 GB. 
 
Import Mode Overview

  • Import Mode supports numerous data sources like Excel, CSV, Social media,  Web URL, CRM data & more as shown below

Power BI Import Mode

Img source: pbiusergroup.com

  • Improved functionality: Users can access all M and DAX functions, format fields as much as they want, and data modelling is completely unrestricted.

 
Direct Mode Overview
Direct query supports a number of data sources like SQL server, Azure data  bricks, Amazon Redshift & more as shown in below image.

Direct Query Data Sources in Power BI

Img source: pbiusergroup.com

  • Data Scheduling: Data is queried from the data source, ensuring that you get the most up-to-date information. Every 15 minutes, the report is refreshed.
  • Files with a smaller footprint: The Power BI Desktop files are much smaller and easier to work with because you are not caching your data when using Direct Query (faster saving, publishing etc.)
  • Storage space is limited
  • You don’t have to store that compressed data on Power BI Service if you don’t have a cache, thus you don’t need as much storage capacity on Service.

 

Power BI Changing Storage Modes (i.e. Power BI change from Direct Query to Import)

Here is how you can change storage modes: Power BI change from direct query to import

  • If you are in “Direct Query “Storage Mode: DirectQuery (click to change)” is normally displayed in the bottom right corner of the Desktop Application.”
  • You can also be in mixed mode, which will display the notification “Storage Mode: Mixed (click to change)”.
  • Another approach to figure out what Storage Mode a table is in is to pick it, right-click, and select Properties.
  • If you go to the Edit Query page and click “Switch all tables to import mode,” you’ll be given the choice to make the modification. A pop-up box will appear, notifying you that this is irreversible. If you switch to Import, the window warns you that there may be consequences, and that “setting storage is an irreversible procedure.” It will not be possible to revert to DirectQuery.”
  • Simply choose OK at the bottom of the box if you’re moving ALL tables to import mode.

Another option to switch to a different storage mode is to go to the table, right-click on properties, and afterwards select the storage mode you want for that table.
 

Limitations of Import vs Direct Query 

Import & Direct Query Limitations

 

DirectQuery

  • Timeliness of Data

Data is queried directly from the data source, ensuring that you have the most recent information. Every 15 minutes, the report is refreshed. Each 15 minutes, the report is refreshed.

  • File Size is Reduced

Your Power BI Desktop files are much smaller and easier to work with because you aren’t caching your data when using Direct Query (faster saving, publishing etc.)

  • Storage requirements are reduced

You won’t have to store the compressed data on Power BI Service if you don’t have a cache, so you won’t need as much storage on Service.

Import

  • Data that has been delayed

You can schedule up to 8 refreshes each day (more with Premium SKUs), but keep in mind the number of reports you’re updating, and the size of the data sets you’re renewing.

  • Data Size Restrictions

The size of each dataset’s import cache is limited to 1GB (can be increased in Premium). While the Vertipaq engine does an excellent job at compressing, you should keep this in mind while selecting your connection method.

  • Unable to switch back

Surprisingly, once you’ve chosen Import, you can’t go back to Direct Query. So, before making the switch, make sure you want Import, or you’ll be in for a lot more work.
 

Difference between Import Mode & Direct Query

Import Mode Direct Query or Live Connection
Refresh frequency Hourly or daily programmed async jobs Real Time
Performance Because the data model is already cached, there is no visible latency. Because queries are processed in real-time, it is dependent on how quick the network link and data source are.

The Data model just stores metadata and schema structure.

Data storage in Power BI Data is stored in the Power BI Service because it is a cached mode (cloud) The Power BI cloud service will not store any data. The data is kept on-site.
Dataset size (Max) Per model, there is a hard restriction of 1 GB (columnar data compression). There is no limit to the amount of data that can be stored in an on-premises database.
Security On the PBI dataset, users can build row-level security (import only) Using DAX expressions, reuse on-prem row-level security for Analysis Services Tabular.
Target Audience Datasets of Small and Medium Size Massive Datasets (Greater than 1 GB)

Per model

Datasource Support All Data Sources and Transformations are supported.

Multiple data sources are supported.

Power BI Direct Query data sources: Only one data source is supported.

Only supports Filtering in a single direction

Filtering in both directions is not supported.

Max Size 1 GB after compression at the columnar level Per dataset, a maximum of one million rows can be retrieved.

 

How to Change Import Mode to Direct Query Mode in Power BI

Making the switch to DirectQuery from Import mode:

  1. Click Edit Queries to open the Power Query Editor.
  2. Double-check that the top query is selected.
  3. Click on the bottom query while holding down the Shift key; this will select all questions.
  4. Press Ctrl + C on your keyboard.
  5. Open a text editor of your choice (like Notepad).
  6. Hold down the Ctrl key and press the V key.

 

How to Change Direct Query Mode to Import Mode in Power BI

  1. Make a csv file first.
  2. Select csv file from the Get Data menu.
  3. Select Load from the drop-down menu.
  4. A notification stating that all queries must be switched to import mode would be displayed.
  5. Select Switch.
  6. Update the model.

 

Power Apps Visual for Power BI – CDS Direct Query Mode

To begin with, you should keep in mind the perquisites:

 

How to Setup CDS for Direct Query  

Follow the below step to understand the setup process –

  1. Sign into Power Apps, and then select the appropriate environment from the top-right corner.
  2. On the left navigation pane expand Data, select Tables, and then select Analyze in Power BI on the command bar.
  3. The pbids file for your environment is downloaded to your browser’s default download folder.
  4. Open the .pbids file to access it in Power BI Desktop.
  5. The pbids file is loaded in Power BI Desktop. In the dialog box, select Organizational account, select Sign in, and then in the browser window that appears select or enter your credentials.
  6. In the dialog box in Power BI Desktop, select Connect. The environment appears in the Power BI Desktop Navigator window. Expand it to view the tables available to analyze. Select a table to preview its data.
  7. After you’re finished selecting the tables you want to analyze, select Load to build a report.

 

Advantages of Direct Query Mode in Power BI

Using DirectQuery has a number of advantages:

  • DirectQuery allows you to create visualizations from very huge datasets, which would otherwise be impossible to do with pre-aggregation.
  • Changes in the underlying data may necessitate a data refresh. The necessity to display current data in some reports can necessitate huge data transfers, making reimporting data impossible. DirectQuery reports, on the other hand, always use current data.
  • DirectQuery is exempt from the 1-GB dataset limit.

 

WHEN to use DirectQuery?

“real-time” or “near real-time

If you require “real-time” or “near real-time” data, DirectQuery mode should be considered. As Import mode saves a snapshot of your data, it must be refreshed on a regular basis to acquire the most up-to-date information. Import mode is nearly impossible to use if you need data with a maximum 1-minute latency.

When the data size is so huge that you can’t fit it into a maximum .pbix file size, then you should use use DirectQuery. Data stays in the source with DirectQuery, and your aggregations/calculations are done well before the modified results are returned to your report.

WHY (not) to use DirectQuery?

Performance: When utilizing DirectQuery, the most important thing to remember is that the overall user experience is nearly entirely dependent on the performance of the actual data source. If the source database is not optimized for analytic workload, the performance of reports will suffer.

Too many people engaged with a report: In addition, the number of people who engage with the report at the same time will have an effect.

And, even if you think there’s a good chance you can improve those two by using different strategies, take into account that there are certain things you can’t really control, such as:

  • Network delay
  • Performance of the source server

 

How to connect using DirectQuery?

The connection dialogue box allows you choose how to connect to a data source supported by DirectQuery when you use Get data. Select Get data > SQL Server from the Home ribbon in Power BI Desktop. The Data Connectivity mode in the SQL Server Database dialogue box displays the Import and DirectQuery options:
 

Power BI Desktop as an Analytical Interface

Sql Data analytics

  1. Launch a new Power BI Desktop App.
  2. Select SQL Server from the Power BI Desktop ribbon’s little triangle at the bottom of the Get Data button. A dialogue box for SQL Server Database will display.
  3. In the Server text box, type the name of the server. This would be the name of your SQL Server or one of your organization’s SQL Server resources.
  4. If you have one, enter the name of the database.
  5. Make sure the Connect Live button is turned on.
  6. Click the ‘OK’ button. After that, the Navigator dialogue box will display.
  7. Click the ‘OK’ button. You’ll see the Power BI Desktop Report view.

 

Which method is best & fastest – Direct vs Import?

Import Data gives you access to all of Power BI’s features. Full Power Query transformations, DAX measurements, and visualizations are all available. DirectQuery will present you with a number of Powers Query alternatives.

The size limit for the Import Data method is 1GB per model. As a result, without Power BI Premium, this technique is not as Scale-able.

You get better scalability with DirectQuery and Live Connection. A vast amount of data can be stored in data sources.

As a result, Live Connection and DirectQuery are the winners when it comes to scalability.

 
Conclusion:
To summarize, when it comes to Power BI import vs direct query, both Data Connectivity options, Import Mode and DirectQuery have their own set of features as well as some limits. When the data size is less than 1 GB and the data is not constantly changing, Import Data works well. To acquire the most up-to-date data, you can import data with a scheduled refresh. You may use the high-performance query engine to its full potential with the Import connection.

DirectQuery Connectivity mode allows you to connect to data directly. When the data changes frequently and the data volume is big, DirectQuery mode is utilized to construct real-time or near real-time BI solutions. We’ve tried to include as much information as possible to assist you in making the best choice for your organization’s connection needs because in the end It all boils down to whatever approach best suits your organizational objectives.

Difference Between Slicer & Filter in Power BI

Basic Date Formatting At the most basic level of formatting, the distinction between filters and slicers is obvious. You can use traditional controls like the checkbox or complex date slicers like the relative slicer. It enables you to specify a specific range of days, such as today or yesterday, current or previous year, etc., without […]

Power BI Slicer vs Filter

Basic Date Formatting

At the most basic level of formatting, the distinction between filters and slicers is obvious. You can use traditional controls like the checkbox or complex date slicers like the relative slicer. It enables you to specify a specific range of days, such as today or yesterday, current or previous year, etc., without having to specify a specific range of days. A similar option is available in the filter panel, which includes relative date filters. Furthermore, it appears that the advanced setting filters offer far more versatility than the original slicer. With some similarities & differences let’s find how slicer vs filter work in Power BI. 

Slicers take up an important real estate on report page, but they don’t appear to provide enough value to be chosen before filters. Even though the filtering window can cover most of our fundamental data filtering needs in visualization, there are a few instances why we might still prefer to use slicers when building reports.

Slicer synchronization

Filters can now be applied to a single visualization, all visualizations on a page, or the full report (visual level, page level, and report level filters). Slicers, in turn, allow you to pick which party reports to filter and which page is displayed to the user by utilizing the selective sync option (tab “View” -> optionSlicers synchronization). This gives you a lot of freedom and makes solving unique modeling challenges a breeze.

You can use this option to create a consistent history on a report with multiple tabs that we will guide the user through. For example, on the first tab, the user picks the client segment in which he is interested, then moves on to the second tab, where he examines the products, they buy and selects the product categories of value to them that have been purchased by previously defined consumers.

After deciding on the categories of interest, the user can proceed to the third screen, where he can examine allowed discounts and profit margins achieved in previously selected segments on defined products.

Visual Interactions

Users can fine-tune the impact of the slicer on other visualizations in the report using Power BI. The “Edit interaction” in “Format” can be used to control this. You may use this functionality to configure the slicer so that it doesn’t affect our selection of some items while filtering out the rest.

Slicers are the sole way to customize this visual interaction; filters cannot create the same effect. This method makes it simple to construct a mechanism that compares sales from periods 1 and 2. This is justified when the periods are tailored — for example, we wish to compare sales from the last weekend before Christmas on one screen with sales from last Thursday.

Layout and Positioning

Slicers and filters are both to be applied on data pertaining to visual, thus they have the same flexibility of movement as the other visualizations on the report. This is a huge benefit and a huge disadvantage at the same time. If the slicer and another display, such as a chart, are closely related and this layout is more logical for the user, it is sometimes important to position them close together when constructing reports.

The filter window, on the other hand, is fixed to the right side of the report workspace and cannot be moved at the moment. Furthermore, filters are only available in basic and advanced layouts, but slicers have their own set of visualization templates. They can take the form of checkboxes, a horizontal orientation bar, a list, a drop-down menu, sliders, and other elements. Slicers can also be given a name that more accurately suits the context in which they will be utilized.

For example, if the data model has a product table, we can filter by items bought, sold, in stock, or manufactured products, depending on the report. In the case of a filter window, the name from the data model will always be used.

Difference between Filter pane, Slicers & Cross Filtering

1) Cross-filtering 

Power BI’s cross-filtering feature adds interactivity to the page’s visualizations. A user’s selection from other visual triggers a response from the visual. Cross highlighting, which is accessible for a set of graphics such as bar and column charts is likely to be related. The context of the visual total is kept but semi-transparent in the event of cross highlighting. 

By using Ctrl + Click over many visuals, you may create multiple levels of cross-filtering.

Viewers can automatically apply filters to the page utilizing cross filtering without needing any external slicer visuals or the Filter Pane.

Cross-filtering, unlike slicers and filters, doesn’t really keep the selection state. The selection is lost as soon as the user clicks somewhere else, and the related filter is no longer applicable. Slicers and filters applied through the Filter Pane, unlike cross-filtering, keep their selections till the user explicitly alters the values.

In the Options tab, users can set the cross-filtering or cross highlighting experience for the report viewers globally. Users can also modify behavior per-visually by going to the Format tab of the ribbon, Edit Interactions, and changing the icons in the visual header.

2) Filter Pane

The Filter Pane in Power BI is a second way to apply filters. The Filter Pane can be extended, collapsed, or hidden depending on your preference. The width of the Filter Pane can be customized, but the location cannot. It shows on the right side of the report canvas at all times. Power BI allows users to adjust the formatting of the Filter Pane as a whole and for individual filters.

Color may play a vital role in the design of your Filter Pane. Filter Cards on the Filter Pane include a backdrop color property in addition to complementing or accenting the background of your main report. Color may vary depending on whether an individual filter is Available or Applied. When filters are applied, this distinction gives visual cues to highlight when they are applied.

Basic and Advanced Filtering options are available in the Filter pane. On the Filter Pane, you’ll notice a mix of Visual Level Filters (“Filters on this visual”), Page Level Filters (“Filters on this page”), and Report Level Filters (“Filters on all pages”), based on the context of a user’s choice and your decisions as a report author.

Unlike slicers, the Filter Pane allows you to add as many filters as you like without affecting the design of your report canvas. The Filter Pane is a great way to give users some of the extra filters they might want without clogging up the report with slicers.

3) Slicers

Slicers like cross-filtering and the Filter Pane, provide similar filtering results. As a result, using slicers implies reserving space on the page for additional visualizations. As a result, slicers must be taken into account while designing the visual style of your report canvas, and page space are limited. Slicers take up space on your report page that could be used for your main visuals.

While Microsoft chooses where the Filter Pane appears on the report canvas, you have a lot of options for where your slicers appear. A group of slicers is frequently seen at the top, left, or right of a report page. When not in use, you might want to utilize a bookmark approach to “hide” the slicers. Users must also specify whether all filters should be slicers, or whether only the most important filters should be displayed as slicers on a page, while smaller filters should be moved to the Filter Pane. Every choice has a visual impact and affects your user experience.

Slicers should be positioned in such a way that they appear natural to report viewers. However, slicer selection and placement that may be ideal for your visual design may not always be ideal for users. To deliver the optimal user experience, strike a balance between making slicers available while not detracting from the page’s core visuals.

Another choice for slicers is whether to use the core Slicer visual or one of the numerous custom visuals available from the AppSource marketplace because they are visuals. This isn’t only a matter of aesthetics or utility. One thing to keep in mind while picking a custom graphic is that, unlike the built-in slicer, it cannot extend beyond its boundaries.

Key Differences between Cross Filter, Filter & Slicers in Tabular Form

Cross filtering Filter Pane Slicer
State of Selection Temporary Permanent Permanent
Effect on the report page  Built-in to existing visuals Separate panel Additional visual(s)
Interactions Per-visual control Not applicable Per-visual control
Synchronize selections on multiple pages Not valid Report Level Filters Sync Slicers
Visual variety Any visual Not applicable Numerous visual options
Measures can be used to filter the values that are accessible Not valid No Yes
Evaluation of delay for query reduction No Yes, but not default option Yes, but not default option

 

Advantages of using Slicer on a Power BI Report

  1. A slicer is simple to use; they appear on the canvas.
  2. Report consumers can customize the visuals by selecting what they want to see.
  3. Despite the fact that slicers are limited to a single report page, you can add them to many pages and synchronize their activities. 

Advantages of using Filter on a Power BI Report

  1. It has three levels of application: report, page, and visual.
  2. Users can apply a filter to a single object on the page, like tables, charts, or cards.
  3. It can be used inside the same report on different pages.
  4. Users could use a filter to refer to objects in other reports.

Conclusion

It’s a skill to refine Power BI data outcomes. To get the most out of reports, report developers must apply their knowledge. They must employ slicers to provide the appropriate level of end-user accessibility, and they must use various filters to regulate report performance.

 

Direct Query in Power BI: What is it & How Does it Work?

When a dashboard element is created or edited, Direct Query or a direct link to source data is used to retrieve the data source. Direct Query in Power BI allows you to create visualizations from very huge datasets that would otherwise be impossible to do with pre-aggregation. Changes in the underlying data may necessitate a […]

Direct Query Power BI

When a dashboard element is created or edited, Direct Query or a direct link to source data is used to retrieve the data source. Direct Query in Power BI allows you to create visualizations from very huge datasets that would otherwise be impossible to do with pre-aggregation. Changes in the underlying data may necessitate a data refresh.
 

Types of Queries in Power BI

Before data files are brought into Power BI, the Query Editor in Power BI is used to alter or edit them. The Query Editor is a data container that allows you to manipulate data by choosing rows and columns, separating rows and columns, pivoting and unpivoting columns, and so on.

Home, Transform, Add Column, View, Tools, and Help are the four tabs on the Power Query Editor ribbon.

Power BI Query Editor

source: docs.microsoft.com
The Transform tab allows you to perform common data transformation activities like:

  • Columns can be added or removed.
  • Altering the data kinds
  • Dividing column
  • Additional data-driven tasks

 

What is Direct Query?

Power BI Direct Query is a data retrieval mechanism that gets data from the data source immediately at query time. The final half of the statement is crucial: although Import mode maintains an in-memory snapshot of your data, Direct Query (DQ) does not. It goes straight to the data source (which is 99 percent of situations is SQL database) and pulls the data from there for each request.
 

Which Data Sources Support Direct Query?

Power BI Direct Query is only supported by a few data sources, unlike Import Data, which is supported by all sorts of data sources. A Direct Query connection to an Excel File is not possible. Direct Query mode is usually supported by data sources that are relational database models or have a modeling engine.

Some of the Power BI Direct Query sources are:

  • Redshift (Amazon)
  • Google BigQuery (Beta)
  • IBM Netezza (Beta)
  • Impala (Beta)
  • Azure HDInsight Spark (Beta)
  • Azure SQL Database (Beta)
  • Azure SQL Data Warehouse (Beta) (version 2.x)
  • Database Oracle (version 12 and above)
  • SAP Enterprise Resource Planning (ERP) (Beta)
  • SAP HANA
  • Snowflake (Beta)
  • Spark (version 0.9 and above)
  • Teradata Database
  • SQL Server
  • Vertica (Beta)

 

How Direct Query Works in Power BI?

When you enable Power BI Direct Query, Power BI makes a query to the data source every time you see a visualization and the result is returned. SQL Profiler can be used to examine this procedure. SQL Profiler is a program that allows you to record SQL Server database queries.

SQL Profiler

img source: mssqltips.com

In the SQL Profiler, you can see that queries were sent to the database. Each visualization has its query. Even if two visualizations display the same item, two messages are sent to separate queries to the database.
 
Power Query Editor Elements

a. Column Navigation

Column Navigation in Power BI

b. Row Navigation

Row navigation Power BI

c. Cell Navigation

cell navigation power query

img source: thepoweruser.com
 

How Do You Write a Direct Query in Power BI?

When a dashboard element (or graphic) is created or edited, Power BI Direct Query or a direct link to source data is used to retrieve the data source. This approach contrasts with the more “traditional” approach used with Power BI, which involves connecting to a data source, transforming, filtering, and modeling a small portion of the data, and then putting all of the required data into the Power BI compressed in-memory data model.

If you’ve used Power BI before, you’ll know that loading and compressing huge data sets from several relational database tables or data warehouse fact and dimension tables can take a long time.

Although using a powerful workstation with loads of RAM, an industrial-strength server, and a fast network can speed up this procedure, it can also slow down your creative data analysis. 

Performance Optimization in Direct Query

You can optimize performance by keeping track of them. You can keep track of performance to spot bottlenecks. The focus of ongoing optimization should be on slow queries—or report visualizations. In Power BI Desktop, monitoring can be done at design time, or on production workloads in Power BI Premium capacity.&nbsp:

Benefits of using Direct Query in Power BI

Using Power BI Direct Query has a number of advantages:

  • Direct Query allows you to create visualizations from very huge datasets, which would otherwise be impossible to do with pre-aggregation.
  • Changes in the underlying data may necessitate a data refresh. The necessity to display current data in some reports can necessitate huge data transfers, making re-importing data impossible. Direct Query reports, on the other hand, always use current data.
  • Direct Query is exempt from the 1-GB dataset limit.

 

Power BI Direct Query Limitations

Limitations of Direct Query in Power BI are

  • An error occurs if the Power Query Editor query is extremely complex. Either eliminate the problematic step in Power Query Editor or import the data instead of using Direct Query to fix the error. There is no Power Query Editor for multi-dimensional sources like SAP Business Warehouse.
  • The Power BI Service does not support calculated tables and calculated columns that link a Direct Query table from a data source using Single Sign-on (SSO) authentication.
  • Direct Query does not support auto date/time. In Direct Query mode, for example, particular treatment of date columns (drilling down by year, quarter, month, or day) is not possible.
  • Cloud sources (which are any data sources that are not on-premises) are limited to one million rows, while on-premises sources are limited to a defined payload of around 4 MB per row (based on proprietary compression technology) or 16MB data size for the full visual. When using Premium capacity, some constraints may be raised. The restriction has no bearing on the aggregations or calculations used to generate the Direct Query dataset. It only has an impact on the rows returned. Maximum row limits can be established by premium capacity.
  • For Direct Query sources, there is a 125-column limit in a table or matrix for results with more than 500 rows. A scrollbar appears when presenting a result in a table or matrix with more than 500 rows, allowing you to download more data. In that case, the table or matrix can have a maximum of 125 columns. If you need to add more than 125 columns in a single table or matrix, try utilizing MIN, MAX, FIRST, or LAST to create measures that do not count towards the 125-column limit.

 

Security Warning of Power BI Direct Query

A security warning popup will appear if you use Power BI Direct Query datasets with Azure Analysis Services (AAS).

The same security caution applies to mixing Direct Query and import sources in a data model: data can be pushed from one data source to the other.

In-Memory analytics in Power BI Direct Query

In-Memory with Power BI Direct Query is a hybrid model. By default, queries should be replied to using the In-Memory mode; nevertheless, the client’s connection string might specify the Direct Query mode instead.
 

Power BI & Direct Query Best Practices

When utilizing Direct Query mode in Power BI, you should follow the recommended practices listed below –

  • Avoid complex Power Query transformations – Power Query will construct a query and send it to a source database each time you apply a transformation to your data model. If you must utilize calculated columns, make an effort to create them on the source database and keep them durable.
  • Avoid using complex DAX measures – as your DAX statement must be “translated” into SQL, this can result in expensive SQL queries. If at all feasible, do all of your calculations on the source site.
  • Avoid relationships on GUID columns (unique identifier) – Power BI doesn’t support this data type and requires data conversion during query execution, which slows down the query. The solution is to convert this data type before Power BI generates its own queries in the source database.

When possible, limit parallelism — you can specify the maximum number of connections that DQ can open at once.
 

Power BI: Import Mode Vs Direct Query Mode

Import Mode:

Import Data Connectivity mode allows you to import data into the Power BI cache. When the data size is less than 1 GB and the data is not constantly changing, it is highly advised to use the Import connection. To acquire the most up-to-date data, you can import data with a scheduled refresh. You may use the high-performance query engine to its full potential with the Import connection.

DirectQuery:
Direct Query Connectivity mode allows you to connect to data directly. When the data changes frequently and the data volume is big, Direct Query mode is utilized to construct real-time or near real-time BI solutions. There will be no data imported into Power BI. Instead, whether you create a visual or interact with one, Power BI will send queries to the data source. Each query must return no more than or equal to one million rows.
 

How and When to Use Power BI Row-Level Security?

Setup of Row-Level Security in Power BI:
Static RLS:

  1. Launch the Power BI Report
  2. Go to ‘Modeling’ and then ‘Manage Roles.
  3. Choose Create’ and give your new position a name.
  4. Select the vertical ellipsis under ‘Tables’ and then ‘Add filter’. Choose the table field you wish to filter by.
  5. Include reasoning for the criteria you wish to use to filter the field. Check the box (‘Verify DAX Expression’) and then click Save.
  6. Validate: Choose ‘Modeling,’ then ‘View As,’ than the role you just created. Check that the data is filtering as expected by selecting ‘Ok.’
  7. Save or publish a Power BI report to the Power BI Workspace.
  8. Navigate to the Power BI Workspace.
  9. Select the vertical ellipsis (‘More choices’) on the dataset (in Orange) and then ‘Security.’
  10. Select ‘Save’ after adding people to the roles you created.
  11. Select the vertical ellipsis adjacent to the security group (‘More options’) and then ‘Test as role. Verify that the RLS is functioning properly.

Dynamic RLS

Directions:

  1. Launch the Power BI Report
  2. Include a dimension table.
  3. Connect the fact table to the dimension table.
  4. Go to ‘Modeling’ and then ‘Manage Roles.’
  5. Click ‘Create.’ Give your new role a name (‘Users’ is a good example).
  6. Select ‘Tables’ from the drop-down menu. Choose the vertical ellipsis option. Choose ‘Add filter’ from the drop-down menu. Choose the field you wish to filter by from the table (‘user email’).
  7. Add logic to filter the field (‘user email = userprincipalename()’) and check the box (‘Verify DAX Expression’). Choose to save.
  8. Validate: Choose ‘Modeling’. Choose ‘View As’. Choose the role you just created. Choose ‘Ok.’ Check to see if the data is filtering as it should.
  9. Save or publish a Power BI report to the Power BI Workspace.
  10. Navigate to the Power BI Workspace.
  11. Select the vertical ellipsis (‘More choices’) on the dataset (in Orange) and then ‘Security.’
  12. Add users to the role you created (‘Users’). Choose ‘Save’.
  13. Select the vertical ellipsis adjacent to the security group (‘More options’) and then ‘Test as role.’ Verify that the RLS is functioning properly.

 

When should I use Row-Level Security?

Consider whether your dataset contains sensitive information to see if RLS is required for your Power BI report (e.g., company financials, customer information, patient information, payroll information, or employee information).

Second, assess whether end-users will need to use filters to find the information they need. If you agree with these assertions, you should use RLS in your report.
 

Determining the queries sent by Power BI Desktop

If you’re not familiar with SQL Server Profiler or other “conventional” tools, you can use Power BI Desktop’s Performance Analyzer capability in conjunction with DAX Studio to achieve the same result.

  • You should be able to view total times for each visual on your report canvas if you simply refresh the visuals:
  • Go straight to a DAX Studio after selecting Copy Query. Scroll down after pasting the copied query into the main window to see the SQL query.
  • Within DAX Studio, enable the “All Queries” button and wait a few moments for the Query trace to begin:
  • Return to Power BI Desktop and refresh the visualizations. After that, reopen DAX Studio and go to the All queries tab:
  • All of your queries will now be collected at the same time, you can also click on an individual query after turning off the All Queries tab and turning on the Server Timings tab:
  • When You double-click the selected query, DAX code appears in the main window, allowing you to click Run and then scroll down to the Server Timings tab:
  • There are several measures relating to server performance, such as CPU time, as well as SQL queries generated by Power BI. You can also look at how long Formula Engine (FE) and Storage Engine (SE) took to complete the request.

 

Understanding the form of the query sent by Power BI Desktop

The left (Queries) pane

The Queries pane on the left shows the number of ongoing queries as well as the query’s name. When you choose a query from the left pane, the data from that query appears in the center pane, where you may shape and change it to match your needs.

The right (Query Settings) pane

All steps related to a query are displayed in the right pane or Query Settings pane.

Implementing a Power BI push dataset solution

Push datasets: The tables are imported into the dataset, and new rows are incrementally added to the tables. This solution has no impact on the data source database performance because there are no additional SQL queries sent to the data source. However, you cannot create push datasets using Power BI Desktop.

You can only create reports connected to published datasets. Push datasets require specific APIs to create the model and update the tables. Push datasets have limitations in the features supported.

Implementation:

The following steps are required for the push dataset to be implemented:

  • As a push dataset, define and deploy the data model.
  • Copy the history data from the imported dataset into the model.
  • Every 30 seconds, write new transactions to the Sales table, grouping the rows in a single POST rows call.

The first two steps can be completed without any programming knowledge. Instead, you can use the Power BI Push Tools that we previously described. This open-source project consists of the following components:

  • A library that makes it easier to use the Power BI API libraries to develop and maintain a push dataset model.
  • Create, maintain, and test a push dataset with this command-line tool.

 

Behavior in the Power BI Service

When using Get Data in Power BI Desktop to connect to a data source with Direct Query, the connection behaves as follows:

  • The source is chosen during the initial Get Data experience. A set of tables is chosen for relational sources, and each defines a query that logically returns a set of data. Only the source is selected for multidimensional sources like SAP BW.
  • However, no data is imported into the Power BI store when it is loaded. Rather, queries are sent to the underlying data source to retrieve the appropriate data when you create a visual in Power BI Desktop. The amount of time it takes to refresh the visual is determined by the speed of the underlying data source.
  • Any underlying data changes aren’t immediately reflected in any existing visualizations. Refreshing is still required. For each visual, the appropriate queries are resent, and the visual is changed as needed.

 

Publish to the Power BI service

Direct Query-generated reports can be published to the Power BI service. You must supply credentials before the Power BI service presents the published report if the used data source doesn’t require the On-premises data gateway (Azure SQL Database, Azure Synapse Analytics (previously SQL Data Warehouse), or Redshift). To provide the credentials, follow these steps:

  1. Open Power BI and log in.
  2. Select the Settings gear icon in the Power BI service and then the Settings menu item.
  3. Go to the Power BI service’s Settings page, choose the Datasets tab, select the Direct Query dataset, and then click Edit credentials.
  4. Fill in your credentials. Otherwise, when you open a published report or explore a dataset produced with a Direct Query connection, an error occurs.

Install an On-premises data gateway and register the data source for data sources other than Azure SQL Database, Azure Synapse Analytics (previously SQL Data Warehouse), Redshift, or Snowflake Data Warehouse that use Direct Query.
 

Conclusion

If you’re considering utilizing Direct Query, you might as well conclude that choosing the correct tool for the job is the best possible recommendation. Before making a final decision, thoroughly consider your potential workloads and try to find the pros and cons of the Direct Query approaches.

Dynamic Row Level Security in Power BI

One of the most efficient and practical methods to restrict data views throughout an organization is to use Dynamic Row-Level Security (DRLS). DRLS uses DAX methods to filter the dataset depending on the Power BI service user’s log-in information. This enables Power BI report authors to quickly construct filtered data views without having to deal […]

Dynamic Row Level Security

One of the most efficient and practical methods to restrict data views throughout an organization is to use Dynamic Row-Level Security (DRLS). DRLS uses DAX methods to filter the dataset depending on the Power BI service user’s log-in information. This enables Power BI report authors to quickly construct filtered data views without having to deal with the headaches of setting up various security roles in the model and maintaining user assignments to these roles.

This article will tell you all about benefits of dynamic row level security in power bi. First we need  to understand the basic difference between Static RLS & Dynamic RLS.

Static RLS implies that you define security logic inside a Power BI file (PBIX), and you must open the PBIX file, make the modification, save the file, and publish it again for each update.

The term “dynamic RLS” refers to the fact that the security logic is defined within the data model (tables, their relationships, etc.). All you have to do to update the logic is add/edit/delete records in the tables.

The below table will help you to understand the concept better.

Static Row Level Security Dynamic Row Level Security
 

Static RLS signifies that you define security logic inside a Power  BI file (PBIX), and you must open the PBIX file, make the  modification, save the file, and publish it again for each update.

 

The term “dynamic RLS” refers to the fact that the security logic is  defined within the data model (tables, their relationships, etc). To  update the logic, simply add, amend, or delete records in the tables.

 

 Static row-level security is simple to build, but it would be a   nightmare to manage if you have thousands of jobs.

 

 In such cases, Dynamic Row-Level Security is the solution.

 

In addition to above, check this video to understand more about Row level security in Power BI.

 

How to implement DRLS?

  • Begin by retrieving the USER ID from the Users table.
  • Select Columns of Region ID from Table Returned and then filter the user region table based on User ID.
  • Create a role that filters the region table using all of the identified Region IDs.

1.Users Table

You’ll need a database of all users in order to get Dynamic Row-Level Security to function. This table must contain all users, as well as a field that contains their Power BI report login id.

If your report is housed on the Power BI service, their login id is the email address they are using to log in. If the report is stored on the Power BI report server, the login id is the network account used to access the server.

2. Roles Table

A table for roles is also required. Keep in mind that roles are dynamically defined within the data model. Each different access level has its own row in the roles table.

It’s worth noting that a separate table for Roles isn’t always necessary. Your user table might also be used as a Roles table. In what instances might you inquire? There is no need for a separate Roles table if each user has only one role. Your Roles table will be a subset of your user table.

3. The Users Table to Filter the Roles Table

If you expect each role to have numerous users assigned to it, you could use a one-to-many connection to filter the roles table from the user table. Users and Roles table on the other hand, frequently form a many-to-many relationship.

4. Role Table Should Filter Other Tables

It’s critical that the role table filters the data model’s other tables. A relationship is used to filter data between Power BI tables. From the roles table, you’ll need a relationship with an appropriate direction to the other tables in the data model.

5. DAX Filters

The DAX expression you create in the role of a filter is another crucial component of dynamic RLS. This DAX expression is used to retrieve the user’s login id and filter the USERS table in your model. The UserName(), UserPrincipalName(), and CustomData() procedures are frequently used in DAX expressions to retrieve user information.

Using Dynamic Row-Level Security with Organizational Hierarchies

Every company has an organizational hierarchy, in many scenarios employees need to be authorized to their data records only, and to the data of people whom they are managing. This is another one of benefits of dynamic row level security. Here is how to use:

  • User Table

The following is a sample of the data in the User table.

As you can see, there are two key columns: the employee’s ID and the Manager’s ID, which refers to the manager’s record.

  • Sales Transaction Table

The transactions table may contain one or more sales transactions for each employee.

  • Sample Report

It contains all employee records as well as the organizational hierarchy.

  • Path Functions in DAX

One of the most typical approaches to provide row-level security in this circumstance is to use Path functions in DAX. Path functions are sophisticated functions that use an ID and Parent ID structure to go across an unknown degree of hierarchy. The structure of your data table is normally built around two columns: ID and Manager ID, as shown below.

  1. Path()This function traverses an ID and parent ID structure, revealing the entire hierarchical route in a string type delimited format. To utilize this function, simply add the following expression to a calculated column in the user table; this function will return the entire hierarchy path as a delimited text value. A vertical line (|) separates the ids of each employee in the path in this text.
  2. PathItem()The function returns the path’s individual item. You can utilize PathItem to find out who the level 1 manager, level 2 manager, or level 3 manager is. This means that calculated columns can be created at any level of the organization’s hierarchy.
  3. PathContains()PathContains is now the most significant function in this post. PathContains will determine whether or not an ID exists in the path. To provide row-level security, you’ll need to use this function. All you need to know is the person’s ID who is logged in.

Find out the ID of person Logged in

In DAX, you may use a Filter function and an Iterator function to determine who logged into the system. The filter function was used to filter the user using the PrincipalUserName() function, and the Iterator function was used to retrieve the ID of the remaining record.

The following is an example of how the Filter Function was used to retrieve the logged-in user’s record. After you’ve located the current user’s record, you can use MaxX or MinX to determine that user’s ID.

Furthermore, you can use this ID in PathContains functions to see if the user’s ID is present in a path. This logic must be added to the User database as a role. This will examine the entire organization hierarchy to determine whether any records in the user table have USER ID in their Path field.

As a result, if you move to that account, you’ll only view logged-in users and their information. You can use different functions and expressions to determine the ID of the current record if you want to utilize another approach of implementing logic.

A Simple Pattern for Dynamic Row-Level Security in Power BI

Let’s put together an RLS using two steps: one calculated column and one measure that describes each person’s function.

1) Employees table query

Keep it simple: if applicable, assign a supervisor to each employee. The data is given as follows in a table:

2) Get dynamic RLS in two steps

The first step is to obtain the table hierarchy by using the path function to a DAX computed column in the employee’s table. The second step entails a little more fiddling. Essentially, we want the Power BI Service to identify

  1. who is connected
  2. return their employee ID
  3. show only the rows where their ID appears in the Hierarchy field.

The answer is to use the following DAX expression in Power BI Desktop’s Manage roles editor.

With:

  1. The function USERPRINCIPALNAME()
  2. The LOOKUPVALUE () method will provide his employee ID “A002” once we match it to his email address, and
  3. The PATHCONTAINS () function will return TRUE for every row where his “A002” ID is contained in the Hierarchy field.

As a result, the employee table is filtered to only include persons who are supervised by Ankit, and he will only see reports for the employees he supervises. 

Configuration of the Security for DRLS in the Power BI Service

In the Power BI Service, set the security for DRLS.

  • Go to the App Workspace where you uploaded the PBIX file and log into the Power BI Service.
  • Go to Datasets, then click on the three dots and select Security.
  • After that, save.
  • Now, when a user logs in, they will only see their own data view.
  • Ideally, the data should be provided through an App from the App Workspace. 

Validating & Publishing in DRLS in Power BI

After you’ve developed your roles, run them using Power BI Desktop to see how they perform.

  1. Select View as from the Modeling tab. You’ll see the roles you’ve established in the View as roles pane.
  1. Choose a position you’ve created, then click OK to apply it. The data is rendered relevant for that role in the report.
  1. You can alternatively choose another user and provide a specific user.

Because the Power BI service and Power BI Report Server use the User Principal Name (UPN), it’s best to provide it.

  1. Other user only displays different results in Power BI Desktop if you’re utilizing dynamic security depending on your DAX expressions.
  2. Click OK.

The report is generated based on what the user is able to see.

Conclusion

To conclude, the dynamic RLS technique is based on the user’s email address, and it restricts the dataset to that user/group of users. Dynamic row level security benefits are countless, and this technique significantly minimizes the amount of Power BI reports and security roles that we must generate, as well as the management of the groups allocated to security requirements once the report is published to Power BI.

Power BI Incremental Refresh: Understanding the Concept & Functionality

When data is LOADED, it is incrementally refreshed, which implies that only the new or updated data is loaded. Power BI Incremental refresh provides automated partition formation and administration for dataset tables that often load new and updated data, extending scheduled refresh procedures. This is typically one or more tables, such as a fact table […]

When data is LOADED, it is incrementally refreshed, which implies that only the new or updated data is loaded. Power BI Incremental refresh provides automated partition formation and administration for dataset tables that often load new and updated data, extending scheduled refresh procedures. This is typically one or more tables, such as a fact table in a relational or star database structure, that store transaction data that changes frequently and can grow rapidly.

Understanding Power BI Incremental Refresh

Incremental Refresh works by partitioning the data in the service’s dataset. Time slices are used to create these partitions and only the data in the most recent partition is refreshed once the data has been imported into the dataset. Two parameters: RangeStart and RangeEnd, must be configured in Power BI Desktop to do this.

Setting Up Incremental Refresh in Power BI Desktop

You will be able to set up incremental refresh in Power BI desktop by connecting to data from a variety of sources using Power BI. The home ribbon is used to connect to data. Select the Get data button label or the down arrow to bring up the most common data types menu. 

Show the most common data types menu and click more to open the Get Data dialogue box. By selecting the Get data icon directly, you can open the Get Data dialogue box without having to go through the most common menu.

Enable the Preview Feature

The first step is to use Power BI Desktop’s Preview capabilities to enable the Incremental refresh Policies.

  • Navigate to File -> Options and Settings -> Options in Power BI Desktop to enable the preview option.
  • Then select Incremental Refresh Policies from the Preview features menu. After that, Restart Power BI Desktop.

Incremental Refresh Option Power BI

Img Source: Powerbiusers.com

Configuring the DateTime parameters

  • Open Power Query Editor in Power BI Desktop by clicking Transform data.
  • Select Manage Parameters > New Parameter from the drop-down menu.
  • In the Power BI Desktop, right-click on the table and choose Incremental Refresh.
  • You can choose the table first in the Incremental Refresh settings window. You won’t be able to do the setting for the table if it doesn’t have the two parameters of RangeStart and RangeEnd used in the filter criterion.

New parameter Power BI

  • In Manage Parameters > Name, write RangeStart (case sensitive), then pick Date/Time from Type, and then enter a start date/time value in Current Value.

StartDate parameter

  • Add a RangeEnd parameter to the mix. Select date/time as the Type, and then enter an end date/time value in Current Value.
    Now that you have RangeStart and RangeEnd parameters, you can use them to filter the data that will be put into the model.
  • The incremental refresh is simple to set up. You just set the number of rows to store (load once and save) and the number of rows to Refresh (re-load every time)
  • Connecting the data source with Power BI Desktop

Filtering the fact table

Each table in the new dataset has a single partition when you publish a Power BI Desktop model to the service. All of the rows for that table are contained in that single partition. If the table is large, such as with tens of millions or even billions of rows, a refresh might take a long time and require a lot of resources.

The service dynamically splits and separates data that has to be renewed regularly from data that can be refreshed less frequently with the incremental refresh. Power Query date/time parameters with the stored RangeStart and RangeEnd are used to filter table data.

Defining Refresh Policy in BI

Once filters have been implemented and a set of data has been imported into the model, users will define an incremental refresh policy for the table. The service uses the policy to construct and manage table partitions and conduct refresh actions after the model have been published to the service. The Incremental refresh and real-time data dialogue box will be used to create the policy, allowing you to provide both necessary and optional settings.

Verifying Incremental Refresh: How to check if it working?

You can test whether incremental refresh is working or not by deploying it to a table and comparing it to a table that does not have incremental refresh deployed to it. The table with incremental refresh will have multiple partitions for current and previous years, whereas the table without incremental refresh will only have one partition. Precaution to take before performing action

Here are some prerequisites for using Power BI’s incremental refresh feature:

  • Import Data Mode: In Import Data mode, the data should be imported into the Power BI data model. The Direct Query mode does not support incremental refresh.
  • Table with Date Field: You’ll need a table (or more) containing date field(s) in order to set up the incremental refresh. The date field is the one that will be affected by the partial data refresh.
  • Licensing Requirement: You’ll also need a Power BI Premium subscription. You won’t be able to use this feature if the file is not in a workspace with premium capacity when you publish it to the service if you configure without the subscription.

Note: Incremental refresh without query folding, according to Microsoft, can result in worse performance.

Publish to Power BI Service Schedule Refresh Frequency

You publish the model to the service after defining the incremental refresh policy. After you’ve finished publishing, you can refresh the dataset for the first time.

If you suspect a dataset will grow beyond 1 GB or more in a workspace with Premium capacity, you can increase refresh operation efficiency and ensure the dataset doesn’t exceed size limitations by enabling a large dataset storage format before completing the first refresh operation in the service.

Limitations: Things to Know Beforehand

After setting up incremental refresh in Power BI, you will no longer be able to get the PBIX file from the service because the data has been partitioned. It also makes sense because the data is probably too large to be downloaded.

Natives Queries or ODBC with Incremental Refresh in Power BI

What is Query Folding and Why Should You Care About It?

Query folding is critical for the incremental refresh, to the point where Power BI will notify you if it can’t be done. It won’t break your incremental refresh “per se,” but without query folding, it won’t serve its main purpose – reducing the amount of data that has to be refreshed in your data model — because the Mashup engine will have to recover all data from the source and thereafter apply subsequent steps to filter the data.

Given the different levels of query-folding support for each data source, it’s a good idea to double-check that the filter logic is included in the source queries. The ideal method to see if a query folds or not is to right-click on it and look at what the View Native Query option looks like: This step (probably) does not fold if it is greyed out. When the View Native Query is greyed out, your query probably doesn’t fold, but it’s not 100% correct. 

If, on the other hand, you are able to select this option, your query will most likely fold again. You might be perplexed by the word: Probably. But that’s the proper word, as you can’t be 100% sure that if the View Native Query option is disabled, that query doesn’t fold.

Keep The Existing Data in Your Power BI Dataset and Add New Data To It Using Incremental Refresh

  1. Turn on the “Incremental refresh” slider.
  2. Select Days from the menu under “Store rows in the last” and enter the number of days you wish to store data for. Data that is older than the specified number of days will be removed from the dataset.
  3. Select Days from the menu under “Refresh rows in the last” and enter 1 under “Refresh rows in the last.”
  4. Leave the boxes for “Detect data changes” and “Only refresh complete day” unchecked.

Power BI Incremental Refresh with Web API Data Source

  1. Use the Web to query the API data source. The Query argument is used in the Contents function, which is very significant.
  2. Create a URL dynamically, including the query string parameters. Using the Query argument allows you to dynamically add the parameters while retaining a valid base URL, which is critical if you want Power BI to check the connection before refreshing the data.
  3. The query string arguments are added to the base URL when the Power BI dataset is refreshed.
  4. You’ll be able to construct a table in your Power BI data model that will be configured for an incremental refresh after the function has been created.

Conclusion

In Power BI, setting up incremental refresh involves loading only a portion of the data on a regular basis and keeping the consistent data. This procedure will significantly reduce your refresh time; however, it does have some prerequisites. A date field is required in your table, and there is now a licensing requirement for Power BI premium to do so. This setting can be done on a Power BI dataset or in Power BI dataflows. If you do this with a dataset, you won’t be able to download the PBIX file once it’s been published.

Contact Us

  • break

Call For Your Free Consultation
1.866.667.1368