Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 23
GenAffair, Inc. Case Study
This is a case study. Case studies are not timed separately. You can use as much test time as you would like to complete each case. However, there may be additional case studies in this test. You must manage your time to ensure that you are able to complete all questions included in this test in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
A key point to note is each case study question builds on insights from the previous question. You can return to these case study questions and make changes until the test is submitted.
GenAffair, Inc. is an Indian online fashion company that uses Microsoft Power BI for its dashboards and reports. The company plans to leverage data from its operational data stores, flat files, and several other data sources.
GenAffair, Inc. has an online sales Data warehouse with the SQL schema shown in the following exhibit.
A few points to note about the data model:
a. The Date Key / Order Date Key / Ship Date Key fields have the format yyyymmdd.
b. The Month field in the Date table has the format yyyymm. The Month field in the Monthly Sales Target table stores a date (the first day of a month in the format yyyymmdd). For example, 20190301 for March, 20190601 for June, etc.,
c. The Week Key / Week fields have the format yyyyww.
d. Only one Manager is responsible for a State. A Manager can handle more than one state (s).
e. Multiple states are part of a region.
Below are the requirements of various stakeholders using the model:
1. Due to potential performance issues, the engineers at the company want to minimize the use of bidirectional cross-filters in the data model.
2. The company directors would like to analyze orders by the date hierarchy (Year, Quarter, & Month).
3. Ensure no data is lost during any operation. And give preference to an optimal solution.
4. Marketing managers need to analyze the sales report using long dates in the format 01 Tue, January 2019
Q] From the model, you need to create a card visual that displays the no. of. customers working in the IT department that purchased in January.
You develop the below measure:
- IT Customers Purchased in January =
- CALCULATE (
- DISTINCTCOUNT ( Customer[Department] ),
- Customer[Department] = "IT",
- 'Date'[Month] = 201901
- )
However, the model setup/measure is not complete/correct.
Which of the following actions would you perform to populate the correct value in the visual? Select two options.
-
A
Update the measure to use the CROSSFILTER DAX function
-
B
Update the measure to use the COUNT DAX function, instead of DISTINCTCOUNT
-
C
Create a bidirectional cross-filter between the Date and the Sales table
-
D
Create a bidirectional cross-filter between the Customer and the Sales table
Reveal correct answers
Correct answers: A, B
Explanation
Recall from earlier discussion, the relationships between the Sales, Customer, and the Date table.
First, note that we need to display the no. of. customers for a set of given filters in the filter context. But, using the DISTINCTCOUNT of Customer[Department] will output the unique no. of. departments in the Customer table. The result will be 1 as the Department is already filtered down to IT.
Using COUNT will be a more suitable answer.
Option B is one of the correct answers.
But observe that five Customers work in the IT department and the output of the updated measure is also 5.
This could mean that the Customer table is not filtered by Month from the Date table. It’s obvious if you think about the data model, as the month filter from the Date table reaches only up to the Sales table. The month filter does not propagate to the Customer table.
To fix this issue, you can set the cross-filter direction between Customer and Sales to both.
This will output the correct result.
Note: You can find the Verify Measure sheet in the attached Excel workbook.
However one of the case study requirements is to minimize the use of bidirectional cross-filters, where possible. In this case, it is indeed possible for us to activate the bidirectional cross-filter in a measure definition by using the CROSSFILTER DAX function, rather than activating the relationship for the entire model.
So, set the model relationship between Customer and the Sales table to single, and update the measure to use the CROSSFILTER function.
The BOTH value for the third parameter in the CROSSFILTER function will ensure that the month filter from the Sales table propagates to the Customer table.
Option A is the other correct answer.
Reference Link: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering#dimension-to-dimension-analysis
https://dax.guide/crossfilter/
PBIX File Link: GenAffair, Inc. Case Study - 6
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
