Intermediate Power BI test (Intermediate)

Sample questions

5 sample questions from the Intermediate 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. In a Power BI project where sales data is stored in a local currency and needs to be converted to USD for reporting, which of the following is the correct approach to create a calculated column for currency conversion?

   1. Use a DAX measure with the SUMX function to calculate the total USD amount by multiplying the local currency amount by the exchange rate for each row in the Sales table.
   2. Use a measure to calculate the exchange rate dynamically in the report using the RELATED function and multiply the local currency amount by the exchange rate in the visual.
   3. Create a calculated column in the Sales table using the RELATED function to fetch the exchange rate from the Currency table based on the transaction date and currency code, then multiply the local currency amount by the exchange rate to get the amount in USD.
   4. Create a new table in Power Query that merges the Sales and Currency tables based on the currency code and transaction date, then use this merged table for reporting.

   Answer: C. Create a calculated column in the Sales table using the RELATED function to fetch the exchange rate from the Currency table based on the transaction date and currency code, then multiply the local currency amount by the exchange rate to get the amount in USD.

   A calculated column in the Sales table using the RELATED function is the correct approach for currency conversion. This allows you to fetch the exchange rate from the Currency table based on the transaction date and currency code. Multiplying the local currency amount by the exchange rate gives the USD equivalent. Other options are incorrect because they either do not use the correct method for fetching data from a related table or fail to perform the actual conversion.

2. Which of the following statements accurately describes how visual-level filters are applied in Power BI?

   1. Visual-level filters are applied after page-level and report-level filters, but they affect all visuals on the page.
   2. Visual-level filters are applied after page-level and report-level filters, and they only affect the specific visual they are applied to.
   3. Visual-level filters are applied before page-level and report-level filters, and they affect all visuals on the page.
   4. Visual-level filters are applied before page-level and report-level filters, and they affect all visuals in the entire report.

   Answer: B. Visual-level filters are applied after page-level and report-level filters, and they only affect the specific visual they are applied to.

   Visual-level filters are applied last and only affect the specific visual they are applied to. Page-level filters affect all visuals on the page, and report-level filters affect all visuals in the entire report. This hierarchy ensures that visual-level filters have the most specific effect.

3. A user is attempting to create a hierarchy in a single column in Power BI using DAX. Which of the following is the correct approach to accomplish this?

   1. Use the RELATED() function to create a hierarchy between two tables.
   2. Use the PATH() function to create a hierarchy and ensure it is applied to the correct column.
   3. Use the FILTER() function with a calculated column to define the hierarchy.
   4. Use the SUMX() function to calculate the hierarchy levels.

   Answer: B. Use the PATH() function to create a hierarchy and ensure it is applied to the correct column.

   The PATH() function is used in DAX to create a hierarchy, allowing the user to define a parent-child relationship within a single column. This is especially useful for creating a structured hierarchy, such as for organizational charts or nested categories. It is important to apply it to the correct column that contains the hierarchical data to ensure accurate results.

4. What is true about a calculated column in Power BI when it is used for a lookup table?

   1. The calculated column will be computed in real-time for each row as the report is interacted with.
   2. The calculated column will be computed once during data refresh and stored in the model; it will not recalculate dynamically when filters change.
   3. The calculated column will recalculate dynamically when filters change, similar to a measure.
   4. The calculated column will only be computed when the report is viewed in Power BI Service.

   Answer: B. The calculated column will be computed once during data refresh and stored in the model; it will not recalculate dynamically when filters change.

   Calculated columns are computed row by row during data refresh and stored in the model, which means they do not dynamically recalculate when filters or slicers change. Measures, on the other hand, are computed at query time and respond to changes in filter context.

5. Which of the following is the correct approach to detect duplicates in a specific column using a calculated column in Power BI?

   1. Use a calculated column with AVERAGE and RELATED to count duplicates in a specific column
   2. Use a calculated column with SUM and RELATED to count duplicates in a specific column
   3. Use a calculated column with COUNTROWS and FILTER to count duplicates in a specific column
   4. Use a measure with COUNTROWS and FILTER to count duplicates in a specific column

   Answer: C. Use a calculated column with COUNTROWS and FILTER to count duplicates in a specific column

   To detect duplicates in a calculated column, you can use a DAX formula that leverages the COUNTROWS and FILTER functions to count how many times a specific value appears in the column. This is a common approach to identify duplicates. The other options are either incorrect or not applicable for this scenario.