Question 1mediummultiple choice
Read the full Visualize and analyze the data explanation →PL-300 Visualize and analyze the data • Complete Question Bank
Complete PL-300 Visualize and analyze the data question bank — all 0 questions with answers and detailed explanations.
Refer to the exhibit.
```
let
Source = Sql.Database("server", "database"),
SalesTable = Source{[Schema="dbo",Item="Sales"]}[Data],
FilteredRows = Table.SelectRows(SalesTable, each [Year] = 2021),
GroupedRows = Table.Group(FilteredRows, {"ProductID"}, {{"TotalSales", each List.Sum([Amount]), type number}})
in
GroupedRows
```Refer to the exhibit.
```json
{
"Version": "1.0",
"Statement": [
{
"Effect": "Deny",
"Action": [
"Microsoft.PowerBi/datasets/read"
],
"Resource": "*",
"Condition": {
"StringNotEquals": {
"Microsoft.PowerBi/workspace": "finance-workspace-id"
}
}
}
]
}
```Refer to the exhibit.
```
let
Source = Sql.Database("server01", "AdventureWorks"),
Sales = Source{[Schema="dbo",Item="SalesOrderHeader"]}[Data],
#"Filtered Rows" = Table.SelectRows(Sales, each [OrderDate] >= #date(2023,1,1) and [OrderDate] < #date(2024,1,1)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"CustomerID"}, {{"TotalSales", each List.Sum([SubTotal]), type nullable number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"TotalSales", Order.Descending}}),
#"Top Rows" = Table.FirstN(#"Sorted Rows", 10)
in
#"Top Rows"
```Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag a concept onto its matching description — or click a concept then click the description.
Data is copied into Power BI
Queries are sent to the source database
Connects to a published dataset or Analysis Services
Combines Import and DirectQuery sources
Acts as Import or DirectQuery depending on context
Drag a concept onto its matching description — or click a concept then click the description.
SUM
CALCULATE
TOTALYTD
IF
SUMMARIZE
Refer to the exhibit.
```dax
CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL(Date),
Date[Year] = MAX(Date[Year])
)
)
```Refer to the exhibit.
```json
{
"version": "1.0",
"dataset": {
"name": "SalesDataset",
"tables": [
{
"name": "Sales",
"columns": [
{ "name": "SalesAmount", "dataType": "double" },
{ "name": "Date", "dataType": "dateTime" },
{ "name": "Region", "dataType": "string" }
],
"measures": [
{
"name": "Total Sales",
"expression": "SUM(Sales[SalesAmount])"
}
]
}
],
"relationships": []
}
}```Refer to the exhibit. ```dax Sales YoY % = VAR CurrentSales = SUM(Sales[Amount]) VAR PreviousSales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date])) RETURN DIVIDE(CurrentSales - PreviousSales, PreviousSales, 0) ```
Refer to the exhibit.
```json
{
"version": "1.0",
"dataset": {
"name": "Sales Dataset",
"tables": [
{
"name": "Sales",
"columns": [
{ "name": "OrderDate", "dataType": "dateTime" },
{ "name": "Amount", "dataType": "int64" },
{ "name": "ProductID", "dataType": "int64" }
]
},
{
"name": "Product",
"columns": [
{ "name": "ProductID", "dataType": "int64" },
{ "name": "ProductName", "dataType": "string" },
{ "name": "Category", "dataType": "string" }
]
}
],
"relationships": [
{
"fromTable": "Sales",
"fromColumn": "ProductID",
"toTable": "Product",
"toColumn": "ProductID",
"crossFilteringBehavior": "oneDirection"
}
]
}
}
```Refer to the exhibit.
```dax
Sales Rank =
RANKX(
ALL(Sales[ProductID]),
SUM(Sales[Amount]),
,
DESC,
Dense
)
```Refer to the exhibit. ```dax Customer Lifetime Value = VAR TotalRevenue = SUM(Sales[Amount]) VAR CustomerCount = DISTINCTCOUNT(Sales[CustomerID]) RETURN DIVIDE(TotalRevenue, CustomerCount, 0) ```
Refer to the exhibit.
```dax
Sales YoY % =
VAR CurrentSales = SUM(Sales[Amount])
VAR PreviousSales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE(CurrentSales - PreviousSales, PreviousSales)
```Refer to the exhibit.
```json
{
"version": "1.0",
"dataset": {
"tables": [
{
"name": "Sales",
"columns": [
{ "name": "Date", "dataType": "dateTime" },
{ "name": "Amount", "dataType": "int64" },
{ "name": "Region", "dataType": "string" }
]
}
]
}
}
```Refer to the exhibit.
```dax
CALCULATE(
SUM(Sales[Amount]),
Sales[Region] = "West",
USERELATIONSHIP(Sales[DateKey], 'Date'[DateKey])
)
```Refer to the exhibit. ```dax Total Sales = SUM(Sales[Amount]) ```
Refer to the exhibit.
```dax
Sales YoY % =
VAR CurrentYearSales = SUM('Sales'[Amount])
VAR PreviousYearSales = CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE(CurrentYearSales - PreviousYearSales, PreviousYearSales)
```Refer to the exhibit.
```json
{
"version": "2.0",
"dataset": "Sales",
"rows": [
{
"groupName": "Category",
"groupType": "column",
"options": {
"sortBy": "TotalSales",
"sortOrder": "descending",
"limit": 5,
"otherGroup": {
"label": "Other",
"aggregation": "sum"
}
}
}
]
}
```Refer to the exhibit.
```dax
Total Sales =
VAR SelectedDate = MAX('Date'[Date])
VAR SalesToDate =
CALCULATE(
SUM('Sales'[Amount]),
'Date'[Date] <= SelectedDate,
ALL('Date')
)
RETURN
SalesToDate
```Refer to the exhibit.
```dax
Sales LY =
VAR CurrentDate = MAX('Date'[Date])
VAR SamePeriodLastYear = DATE(YEAR(CurrentDate)-1, MONTH(CurrentDate), DAY(CurrentDate))
RETURN
CALCULATE(
SUM(Sales[Amount]),
SAMEPERIODLASTYEAR('Date'[Date])
)
```Refer to the exhibit.
```json
{
"datasetSettings": {
"queryCaching": {
"mode": "On",
"maxCacheAgeInMinutes": 60
}
}
}
```Refer to the exhibit.
```dax
EVALUATE
SUMMARIZECOLUMNS(
'Product'[Category],
"Total Sales", SUM(Sales[Amount])
)
```Refer to the exhibit.
```json
{
"refreshPolicy": {
"mode": "merge",
"incrementalThreshold": 30,
"rollingWindowDays": 10
}
}
```Refer to the exhibit.
```dax
DEFINE
MEASURE Sales[Total Sales] = SUM(Sales[Amount])
MEASURE Sales[Sales PY] = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Year],
"Total Sales", [Total Sales],
"Sales PY", [Sales PY],
"YoY Growth", [Total Sales] - [Sales PY]
)
```Refer to the exhibit.
```dax
VAR LastDate = MAX('Sales'[OrderDate])
VAR PreviousDate = CALCULATE(MAX('Sales'[OrderDate]), ALLEXCEPT('Sales', 'Sales'[CustomerID]))
RETURN
CALCULATE(SUM('Sales'[Amount]), 'Sales'[OrderDate] = PreviousDate)
```Refer to the exhibit.
```dax
EVALUATE
SUMMARIZE(
Sales,
'Product'[Category],
"Total Sales", SUM(Sales[Amount])
)
```Refer to the exhibit.
```json
{
"version": "1.0",
"roles": [
{
"name": "Sales Manager",
"tables": [
{
"table": "Sales",
"filter": "FILTER(Sales, Sales[Region] = \"West\")"
}
]
}
]
}
```You are a Power BI analyst at a retail company. The company has a SQL Server data warehouse with tables: 'Sales' (SalesID, ProductID, CustomerID, SalesDate, Amount, Quantity), 'Products' (ProductID, ProductName, Category, UnitPrice), 'Customers' (CustomerID, CustomerName, Region, Segment). The data warehouse is refreshed nightly. You need to build a Power BI solution that meets the following requirements: - The report must be published to the Power BI service and support scheduled refresh. - Users must be able to analyze sales by product category and customer region. - The data model should follow a star schema design. - A measure must calculate the running total of sales by date. - The report must include a visual that shows the top 5 products by sales amount for the current year. - Performance is critical; the report should load quickly.
You have imported the data into Power BI Desktop. Which of the following actions should you take to meet the requirements?
You are a Power BI data analyst at a global retail company. Your organization uses a SQL Server data warehouse to store sales transactions, product inventory, and customer demographics. The data warehouse is updated nightly. You have been tasked with building a Power BI report to analyze sales performance across different regions and product categories. The report must meet the following requirements:
- Allow users to filter data by date, region, and product category. - Provide drill-down from region to store level. - Display key metrics such as total sales, sales growth compared to the previous year, and profit margin. - Ensure that the report loads quickly even when users apply multiple filters. - Users must be able to export the underlying data to Excel for further analysis. - The report should be accessible on mobile devices with a responsive layout. - You need to implement row-level security so that regional managers can only see data for their own region. - The dataset must support scheduled refresh.
You have imported the necessary tables from the data warehouse using Import mode. You created a date dimension table using DAX and marked it as a date table. You also created a separate table for regions with a column 'RegionManagerEmail' that maps each region to the manager's email address. You plan to use RLS by creating a role that filters the region table based on the user's email address.
After publishing the report to the Power BI service, you notice that the report takes a long time to load when users select a large date range. Additionally, the profit margin measure, which is calculated as DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue])), returns blank for some rows even though both Profit and Revenue have values. You also receive feedback that the mobile layout is not optimized; the visuals are too small and the slicers are not accessible.
You need to address these issues. What should you do?
Refer to the exhibit.
DAX expression:
```
Sales YoY % =
VAR CurrentSales = SUM(Sales[Amount])
VAR PreviousSales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE(CurrentSales - PreviousSales, PreviousSales)
```Refer to the exhibit.
Power Query M code snippet:
```
let
Source = Sql.Database("server", "database"),
Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
#"Filtered Rows" = Table.SelectRows(Sales, each [OrderDate] >= #date(2023,1,1)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"CustomerID"}, {{"Total Sales", each List.Sum([Amount]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Total Sales", Order.Descending}})
in
#"Sorted Rows"Refer to the exhibit.
Power BI XMLA endpoint response snippet:
```
<Return>
<Results>
<Result>
<Type>Measure</Type>
<Name>Total Sales</Name>
<Expression>SUM(Sales[Amount])</Expression>
<FormatString>#,##0</FormatString>
</Result>
<Result>
<Type>Measure</Type>
<Name>Sales YoY</Name>
<Expression>CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))</Expression>
<FormatString>#,##0</FormatString>
</Result>
<Result>
<Type>CalculationGroup</Type>
<Name>Time Intelligence</Name>
<CalculationItems>
<CalculationItem>
<Name>Current</Name>
<Expression>SELECTEDMEASURE()</Expression>
</CalculationItem>
<CalculationItem>
<Name>PY</Name>
<Expression>CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))</Expression>
</CalculationItem>
</CalculationItems>
</Result>
</Results>
</Return>
```Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "North")
Refer to the exhibit.
```
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Year],
'Date'[Quarter],
"Total Sales", SUM('Sales'[Amount])
)
ORDER BY 'Date'[Year], 'Date'[Quarter]
```Refer to the exhibit.
```dax
DEFINE
MEASURE Sales[Total Sales] = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
MEASURE Sales[Total Cost] = SUMX(Sales, Sales[Quantity] * Sales[Unit Cost])
MEASURE Sales[Profit Margin] = DIVIDE([Total Sales] - [Total Cost], [Total Sales])
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Year],
"Sales Amount", [Total Sales],
"Profit Margin %", [Profit Margin]
)
ORDER BY 'Date'[Year]
```Refer to the exhibit.
```dax
EVALUATE
FILTER(
ADDCOLUMNS(
SUMMARIZE(
Sales,
Products[ProductName],
Customers[Country]
),
"SalesAmount", [Total Sales]
),
[SalesAmount] > 10000
)
```You are a Power BI analyst for a financial services company. You have a semantic model that contains a 'Transactions' fact table with millions of rows, and a 'Calendar' date dimension table. You need to create a report page that shows the year-to-date (YTD) total transaction amount compared to the same period last year (SPLY). The report must allow users to select a year from a slicer, and the YTD calculation should always be based on the maximum date in the data for the selected year (i.e., show YTD as of the latest available date). You have created the following measures:
- Total Amount = SUM(Transactions[Amount]) - YTD Amount = TOTALYTD([Total Amount], 'Calendar'[Date]) - SPLY YTD = CALCULATE([YTD Amount], SAMEPERIODLASTYEAR('Calendar'[Date]))
When users select a year from the slicer, the YTD Amount does not correctly show the YTD as of the last date in the selected year; instead, it shows the YTD for each individual date in the context. What is the most likely issue?