Power BI Data Analyst Free Sample Questions

DirectQuery is the optimal choice for this scenario. It connects directly to the Azure SQL Database, avoiding data duplication and ensuring that the report reflects the most current data. Given the large volume of data (billions of records), Import mode would be impractical and lead to slow refreshes. DirectQuery sends queries to the source database, leveraging its processing power to handle large datasets and provide near real-time data with low latency.

Merge Queries is the correct operation. It is equivalent to a join in SQL and allows you to combine two tables based on a matching column, in this case 'CarrierID'. This operation adds columns from one table to another. Appending queries combines tables vertically by adding rows, which is not the desired outcome here. For large tables, merging is generally more efficient than trying to perform lookups row by row.

The 'Split Column by Delimiter' transformation is specifically designed for this purpose. By specifying the hyphen '-' as the delimiter, Power Query will automatically create new columns containing the separated parts of the string. This is the most efficient and direct method for this common data cleaning task.

This is the classic implementation of a role-playing dimension. The 'Calendar' table plays multiple roles (admission calendar and discharge calendar). Power BI only allows one active relationship between two tables. The best practice is to set the primary relationship (e.g., on Admission Date) as active and create an inactive relationship for the secondary date. DAX measures can then activate the inactive relationship on-demand using the USERELATIONSHIP function, allowing for flexible analysis without creating model ambiguity.

Time intelligence functions in DAX, such as TOTALYTD, rely on a properly configured date table. If the 'Calendar' table is not officially marked as a date table in the model properties, the DAX engine cannot use its optimized time intelligence algorithms. Instead, it falls back to a much slower, less efficient calculation method, which becomes a significant performance bottleneck on large fact tables. Marking the table as a date table is a critical optimization step.

This formula uses the CALCULATE function to modify the filter context. The FILTER function iterates over a table specified by the ALL(Products) function. ALL(Products) removes any existing filters from the 'Products' table, ensuring the calculation considers all products initially. FILTER then applies a new condition to include only rows where the category is 'Bikes'. This correctly isolates the 'Bikes' category sales, ignoring other report filters on the 'Products' table.

The ALL function is used to remove filters from a table or columns. To create a measure that is unaffected by the 'Campaign Name' slicer, you would wrap the 'Campaigns' table or the 'Campaign Name' column within an ALL function inside a CALCULATE statement. For example: Total Budget = CALCULATE(SUM(Campaigns[Budget]), ALL(Campaigns)). This removes the filter context applied by the slicer, ensuring the sum is always calculated over all campaigns.

Creating a report page tooltip is the correct solution. You would create a new, smaller report page, enable it as a tooltip in the page settings, and add a visual (like a bar chart) showing sales by salesperson. Then, on the main matrix visual, you would set its tooltip property to use this new report page. This allows for rich, data-driven tooltips that provide additional context on hover.

Dynamic Row-Level Security (RLS) is the most scalable solution. It involves creating a single security role with a DAX filter expression that dynamically filters data based on the user's identity, typically their User Principal Name (UPN) or email. This requires a lookup table that maps users to their respective departments. This approach allows for a single report to serve all departments while ensuring data is securely filtered, and it is much easier to manage than creating separate reports or static roles for each department.

A Live Connection is required when connecting to SSAS, as it creates a direct link to the model without importing data or the model structure into Power BI. Because the SSAS instance is on-premises, an on-premises data gateway (in standard mode) is mandatory to act as a bridge, securely relaying queries from the Power BI service to the on-premises SSAS server. This combination ensures real-time data access.