Advanced Power BI test (Advanced)

Sample questions

5 sample questions from the Advanced Power BI test, with the answers. Samples are taken out of the live test, so you will not meet these when you sit it.

1. Which statement accurately describes how DAX memory usage behaves with variables defined using VAR?

   1. Using CALCULATE with multiple filters increases memory usage due to repeated context transitions.
   2. Variables defined with VAR are computed once and remain constant, reducing memory usage during repeated evaluations.
   3. CALCULATE inside a row context increases memory usage by creating additional filter contexts.
   4. FILTER() is more memory-efficient than column predicates like Sales[Qty] > 10.

   Answer: B. Variables defined with VAR are computed once and remain constant, reducing memory usage during repeated evaluations.

   Variables defined using VAR are evaluated once and stored as constants, which reduces memory usage during repeated evaluations. Other options are incorrect because they either misrepresent the behavior of VAR or focus on unrelated aspects of DAX memory usage.

2. You are implementing row-level security (RLS) for a Power BI report that connects to an external data source containing user and region information. The report should restrict users to data based on their assigned region. How should you implement dynamic RLS to achieve this goal?

   1. Create a dynamic RLS filter using a DAX expression that joins the user's email with a user mapping table related to the region dimension, and apply it to the relevant tables.
   2. Create a role in the Power BI service and assign it to a group based on the user's email, and apply the RLS filter using the email column in the data model.
   3. Use a static RLS filter with a DAX expression that references the user's email directly in the filter condition.
   4. Use a Power Query transformation to filter data based on the signed-in user's email and apply it to the data model.

   Answer: A. Create a dynamic RLS filter using a DAX expression that joins the user's email with a user mapping table related to the region dimension, and apply it to the relevant tables.

   Dynamic RLS allows filtering based on the signed-in user's attributes. To implement it with an external data source, you need to map the user's identity to the relevant dimension using a DAX expression that references a user mapping table. This approach ensures that the RLS filter dynamically adjusts based on the user's identity, allowing access to data based on their role or region. Other options are incorrect because they either describe static RLS, do not properly address the use of a user mapping table, or incorrectly suggest that credentials are used directly in the RLS filter.

3. Consider the following DAX measure used in a SUMX iterator: VAR cur = [Sales] RETURN SUMX(Sales, cur). What is the likely outcome of this measure?

   1. The measure will return incorrect results because the VAR is evaluated once and not updated in the CALCULATE.
   2. The measure will return incorrect results because SUMX does not support VAR.
   3. The measure will work correctly because CALCULATE will re-evaluate the VAR for each row.
   4. The measure will work correctly because the VAR is updated automatically in the SUMX iterator.

   Answer: A. The measure will return incorrect results because the VAR is evaluated once and not updated in the CALCULATE.

   In DAX, variables defined with VAR are evaluated once and remain constant throughout the expression, even if they are used inside a CALCULATE function. This means that the VAR 'cur' will not be recalculated for each row in the SUMX iterator, leading to incorrect results.

4. You are implementing dynamic row-level security (RLS) in a Power BI model, where users should only see data related to their roles, which are defined in an external user-mapping table. The user-mapping table is related to a dimension table in the model. What is the correct approach to ensure that the RLS filter is applied dynamically and works correctly with the relationships in the model?

   1. Use a dynamic RLS filter with a calculated column that maps users to their roles, without involving a dimension table.
   2. Use a dynamic RLS filter that references a user-mapping table related to a dimension, and ensure the relationship is set to 'Apply security filter in both directions' if needed.
   3. Create a static RLS filter using the USERPRINCIPALNAME() function directly in the DAX expression.
   4. Deploy the RLS filter at the report level rather than the dataset level.

   Answer: B. Use a dynamic RLS filter that references a user-mapping table related to a dimension, and ensure the relationship is set to 'Apply security filter in both directions' if needed.

   Dynamic RLS filters typically use the USERPRINCIPALNAME() function to filter based on the signed-in user. To implement this, a user-mapping table is often used, which contains the relationship between user attributes (like email) and dimension attributes (like region or department). This table is then related to the relevant dimension table in the model. If the RLS filter needs to propagate through a bidirectional relationship, the 'Apply security filter in both directions' option must be enabled to ensure that the RLS filter is applied correctly. Other options may be incorrect because they may not use a user-mapping table, fail to account for relationship direction, or use static RLS instead of dynamic RLS.

5. Which DAX filter modifier preserves the outer selection context (e.g., slicer selections) while removing other filters?

   1. ALLSELECTED
   2. CALCULATE
   3. FILTER
   4. ALL

   Answer: A. ALLSELECTED

   ALLSELECTED respects the outer selection context, making it the correct choice for preserving slicer selections. ALL ignores slicer selections, while CALCULATE and FILTER do not behave as filter modifiers in this context.