Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 33
You have a fictitious dataset of the products sourced by Apple Computers from its suppliers all over the world.
The sales managers at Apple have to analyze the Sales Amount for each supplier. To achieve this objective, determine if you need to make any changes to the above data model.
-
A
No change is required
-
B
Set bidirectional cross filter between the
Supplierand theSupplier_Producttable -
C
Set bidirectional cross filter between the
Productand theSupplier_Producttable -
D
Set bidirectional cross filter between the
Salesand theProducttable
Reveal correct answer
Correct answer: C
Explanation
If we analyze Sales Amount by Supplier Name (from the Supplier table), we get repeating numbers for Sales Amount (an indication that something is wrong with the data model).
So, option No change is required is incorrect.
The reason for these repeating numbers is that the Supplier Name filters are not carried all the way through to the Sales table (For Many-to-one relationships, filters propagate only from the one-side).
As seen from the above image, the Supplier table filters the Supplier_Product table. But from there, the filters aren’t carried to the Product table. Go through the below image to understand what happens under the hood.
1. Each cell in the visual sends a query to calculate the total sales amount (for that supplier).
2. But, since the filters do not flow from Supplier_Product to the Product table, the Product table is unfiltered.
3. So, for each cell value (supplier), all the values in the Sales Amount column are summed up. Therefore, you get the same value for each supplier.
From the above explanation, it is evident that we need to set the Cross-filter direction between the Product and the Supplier_Product table to Both. Doing so enables the filters to flow easily across the data model.
The report should now show correct Sales Amount values for each supplier.
A similar example is given in Microsoft documentation. https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many#relate-many-to-many-dimensions
The remaining two options are incorrect because there is no need to set bidirectional filters between other pairs of tables in the data model.
PBIX File Link: Bidirectional cross filter
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
