Dogodek
Power BI DataViz Svetovno prvenstvo
14. feb., 16h - 31. mar., 16h
S 4 možnosti za vstop, bi lahko zmagal konferenčni paket in da bi bilo v ŽIVO Grand Finale v Las Vegasu
Več informacijTa brskalnik ni več podprt.
Izvedite nadgradnjo na Microsoft Edge, če želite izkoristiti vse prednosti najnovejših funkcij, varnostnih posodobitev in tehnične podpore.
This article targets you as a data modeler who works with Power BI Desktop. It provides you with guidance on when to create bi-directional model relationships. A bi-directional relationship is one that filters in both directions.
Opomba
An introduction to model relationships is not covered in this article. If you're not completely familiar with relationships, their properties or how to configure them, we recommend that you first read the Model relationships in Power BI Desktop article.
It's also important that you have an understanding of star schema design. For more information, see Understand star schema and the importance for Power BI.
Generally, we recommend that you minimize the use of bi-directional relationships. That's because they can negatively impact on model query performance, and possibly deliver confusing experiences for your report users.
However, there are three scenarios when bi-directional filtering can solve specific requirements:
Bi-directional relationships play an important role when creating the following two special model relationship types:
Bi-directional relationships can deliver slicers that limit options to where data exists. (If you're familiar with Excel PivotTables and slicers, it's the default behavior when sourcing data from a Power BI semantic model or an Analysis Services model.) To help explain what it means, first consider the following model diagram.
The first table is named Customer
., and it contains three columns: Country-Region
, Customer
, and CustomerCode
. The second table is named Product
, and it contains three columns: Color
, Product
, and SKU
. The third table is named Sales
, and it contains four columns: CustomerCode
, OrderDate
, Quantity
, and SKU
. The Customer
and Product
tables are dimension tables, and each has a one-to-many relationship to the Sales
table. Each relationship filters in a single direction.
To help describe how bi-directional filtering works, the model diagram has been modified to reveal the table rows. All examples in this article are based on this data.
The row details for the three tables are described in the following bulleted list:
Customer
table has two rows:
CustomerCode
CUST-01, Customer
Customer-1, Country-Region
United StatesCustomerCode
CUST-02, Customer
Customer-2, Country-Region
AustraliaProduct
table has three rows:
SKU
CL-01, Product
T-shirt, Color
GreenSKU
CL-02, Product
Jeans, Color
BlueSKU
AC-01, Product
Hat, Color
BlueSales
table has three rows:
OrderDate
January 1 2019, CustomerCode
CUST-01, SKU
CL-01, Quantity
10OrderDate
February 2 2019, CustomerCode
CUST-01, SKU
CL-02, Quantity
20OrderDate
March 3 2019, CustomerCode
CUST-02, SKU
CL-01, Quantity
30Now consider the following report page.
The page consists of two slicers and a card visual. The first slicer is based on the Country-Region
field, and it has two options: Australia and United States. It currently slices by Australia. The second slicer is based on the Product
field, and it has three options: Hat, Jeans, and T-shirt. No items are selected (meaning no products are filtered). The card visual displays a quantity of 30.
When report users slice by Australia, you might want to limit the product slicer to display options where data relates to Australian sales. That's what's meant by showing slicer options "with data". You can achieve this behavior by setting the relationship between the Product
and Sales
tables to filter in both directions.
The product slicer now lists a single option: T-shirt. This option represents the only product sold to Australian customers.
First, we recommend that you consider carefully whether this design works for your report users. Some report users find the experience confusing because they don't understand why slicer options dynamically appear or disappear when they interact with other slicers.
If you do decide to show slicer options "with data", we don't recommend you set up a bi-directional relationships. Bi-directional relationships require more processing and so they can negatively impact on query performance—especially as the number of bi-directional relationships in the model increases.
There's a better way to achieve the same result: Instead of using bi-directional filters, you can apply a visual-level filter to the product slicer itself.
Let's now consider that the relationship between the Product
and Sales
tables no longer filters in both directions. And, the following measure definition has been added to the Sales
table.
Total Quantity = SUM(Sales[Quantity])
To show the product slicer options "with data", it simply needs to be filtered by the Total Quantity
measure by using the "is not blank" condition.
A different scenario involving bi-directional relationships treats a fact table like a bridging table. This way, it supports analyzing dimension table data within the filter context of a different dimension table.
Using the example model in this article, consider how the following questions can be answered:
Both questions can be answered without summarizing data in the bridging fact table. They do, however, require that filters propagate from one dimension table to the other. When filters propagate via the fact table, summarization of dimension table columns can be achieved using the DISTINCTCOUNT DAX function—and possibly the MIN and MAX DAX functions.
As the fact table behaves like a bridging table, you can apply the many-to-many relationship guidance to relate two dimension tables. It will require setting up at least one relationship to filter in both directions. For more information, see Many-to-many relationship guidance.
However, as already described in this article, this design will likely result in a negative impact on performance, and the user experience consequences related to slicer options "with data". So, we recommend that you activate bi-directional filtering in a measure definition by using the CROSSFILTER DAX function instead. You can use the CROSSFILTER function to modify filter directions—or even disable the relationship—during the evaluation of an expression.
Consider the following measure definition added to the Sales
table. In this example, the model relationship between the Customer
and Sales
tables has been set up to filter in a single direction.
Different Countries Sold =
CALCULATE(
DISTINCTCOUNT(Customer[Country-Region]),
CROSSFILTER(
Customer[CustomerCode],
Sales[CustomerCode],
BOTH
)
)
During the evaluation of the Different Countries Sold
measure, the relationship between the Customer
and Sales
tables filters in both directions.
The following table visual present statistics for each product sold. The Quantity
column is simply the sum of quantity values. The Different Countries Sold
column represents the distinct count of country-region values of all customers who have purchased the product.
For more information related to this article, check out the following resources:
Dogodek
Power BI DataViz Svetovno prvenstvo
14. feb., 16h - 31. mar., 16h
S 4 možnosti za vstop, bi lahko zmagal konferenčni paket in da bi bilo v ŽIVO Grand Finale v Las Vegasu
Več informacijUsposabljanje
Modul
Create Power BI model relationships - Training
Power BI model relationships form the basis of a tabular model. Define Power BI model relationships, set up relationships, recognize DAX relationship functions, and describe relationship evaluation.
Potrdilo
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.
Dokumentacija
Many-to-many relationship guidance - Power BI
Guidance for developing many-to-many model relationships.
Active vs inactive relationship guidance - Power BI
Guidance for using active or inactive model relationships.
One-to-one relationship guidance - Power BI
Guidance for understanding, developing, and working with one-to-one model relationships in Power BI.