PL-300 Model the data — All Questions With Answers

Refer to the exhibit.

Exhibit: DAX query from Performance Analyzer
EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Year],
    'Product'[Category],
    "Total Sales", [Sales Amount]
)
ORDER BY 'Date'[Year], 'Product'[Category]

This query returns the following error: "The expression references multiple columns. Multiple columns cannot be converted to a scalar value."

Refer to the exhibit.

Exhibit: Power Query M code snippet
let
    Source = Sql.Database("Server01", "AdventureWorks"),
    Sales = Source{[Schema="Sales",Item="SalesOrderHeader"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Sales, each [OrderDate] >= #date(2020,1,1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"CustomerID"}, {{"TotalDue", each List.Sum([TotalDue]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"TotalDue", Order.Descending}})
in
    #"Sorted Rows"

Refer to the exhibit.

```
let
    Source = Sql.Database("server1", "AdventureWorks"),
    SalesTable = Source{[Schema="dbo",Item="Sales"]}[Data],
    FilteredRows = Table.SelectRows(SalesTable, each [OrderDate] >= #date(2022,1,1) and [OrderDate] <= #date(2022,12,31)),
    GroupedRows = Table.Group(FilteredRows, {"ProductID"}, {{"TotalRevenue", each List.Sum([Revenue]), type number}}),
    SortedRows = Table.Sort(GroupedRows,{{"TotalRevenue", Order.Descending}}),
    Top10 = Table.FirstN(SortedRows,10)
in
    Top10

Refer to the exhibit.
```json
{
  "relationships": [
    {
      "fromTable": "Sales",
      "fromColumn": "ProductID",
      "toTable": "Product",
      "toColumn": "ProductID",
      "crossFilteringBehavior": "oneDirection"
    },
    {
      "fromTable": "Sales",
      "fromColumn": "CustomerID",
      "toTable": "Customer",
      "toColumn": "CustomerID",
      "crossFilteringBehavior": "oneDirection"
    },
    {
      "fromTable": "Product",
      "fromColumn": "CategoryID",
      "toTable": "Category",
      "toColumn": "CategoryID",
      "crossFilteringBehavior": "both"
    }
  ]
}
```

Refer to the exhibit.
```dax
EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Year],
    'Product'[Category],
    "Total Sales", CALCULATE(SUM('Sales'[Amount]), FILTER('Sales', 'Sales'[Amount] > 100))
)
```

Refer to the exhibit.
```json
{
  "tables": [
    {
      "name": "Sales",
      "columns": [
        {"name": "OrderID", "dataType": "int"},
        {"name": "OrderDate", "dataType": "datetime"},
        {"name": "Amount", "dataType": "decimal"}
      ],
      "partitions": [
        {
          "name": "Partition1",
          "source": {
            "type": "m",
            "expression": "let Source = Sql.Database(\"server\", \"db\"), Sales = Source{[Schema=\"dbo\",Item=\"Sales\"]}[Data], FilteredRows = Table.SelectRows(Sales, each [OrderDate] >= #datetime(2020,1,1) and [OrderDate] < #datetime(2021,1,1)) in FilteredRows"
          }
        },
        {
          "name": "Partition2",
          "source": {
            "type": "m",
            "expression": "let Source = Sql.Database(\"server\", \"db\"), Sales = Source{[Schema=\"dbo\",Item=\"Sales\"]}[Data], FilteredRows = Table.SelectRows(Sales, each [OrderDate] >= #datetime(2021,1,1) and [OrderDate] < #datetime(2022,1,1)) in FilteredRows"
          }
        }
      ]
    }
  ]
}
```

Refer to the exhibit.

```dax
Sales YTD = 
CALCULATE(
    SUM(Sales[Amount]),
    DATESYTD('Date'[Date])
)
```

The model has a Date table marked as a date table and a relationship to Sales[OrderDate]. However, the measure returns BLANK for all months except December. What is the most likely cause?

Refer to the exhibit.

```json
{
  "dataAccessScope": "Workspace",
  "permissions": [
    {
      "role": "Viewer",
      "dataAccess": "ReadOnly"
    }
  ],
  "excludedTables": ["EmployeeSalaries"]
}
```

You are configuring a Power BI data model for a workspace containing a semantic model with sensitive salary data. The JSON policy above is applied. A viewer reports that they can see the EmployeeSalaries table in the report. What is the most likely reason?

Refer to the exhibit.

```dax
DEFINE
    VAR SelectedDate = MAX('Date'[Date])
    VAR PriorDate = DATEADD('Date'[Date], -1, MONTH)
    VAR PriorSales = 
        CALCULATE(
            SUM(Sales[Amount]),
            FILTER(ALL('Date'), 'Date'[Date] = PriorDate)
        )
    RETURN
        IF(
            PriorSales = 0,
            BLANK(),
            ( SUM(Sales[Amount]) - PriorSales ) / PriorSales
        )
```

You are reviewing a DAX measure for month-over-month sales growth. The measure returns BLANK for all months. What is the likely issue?

Refer to the exhibit.

```json
{
  "name": "RLS Sales",
  "rules": [
    {
      "table": "Sales",
      "filterExpression": "[Region] = USERPRINCIPALNAME()"
    }
  ]
}
```

You apply the above RLS rule to a semantic model. The rule is intended to restrict sales data by the user's region, which is stored in the user's email domain (e.g., user@west.contoso.com). However, the rule does not filter any rows. What is the most likely issue?

{
  "name": "policy1",
  "policyType": "TableLevel",
  "roles": [
    {
      "roleName": "SalesRegion",
      "filterExpression": "[Region] = \"North\""
    }
  ],
  "tables": ["Sales"],
  "filterExpression": "[Region] = \"South\""
}

{
  "name": "RLS Policy",
  "roles": [
    {
      "roleName": "Manager",
      "filterExpression": "[Department] = \"Sales\""
    },
    {
      "roleName": "Executive",
      "filterExpression": "[Department] = \"Sales\" OR [Department] = \"Marketing\""
    }
  ]
}

Refer to the exhibit.

Sales table: OrderDate, ShipDate, Amount
Calendar table: Date

Relationship: Sales[OrderDate] -> Calendar[Date] (active), Sales[ShipDate] -> Calendar[Date] (inactive)

DAX measure:
TotalShipments = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Refer to the exhibit.

Power BI JSON policy for a table 'Sales':
{
  "name": "SalesPolicy",
  "filter": "Sales[Amount] > 1000"
}

Refer to the exhibit.

DAX expression:
CALCULATE(
    SUM(Sales[Amount]),
    KEEPFILTERS(Sales[Region] = "West"),
    ALL(Sales[Region])
)

Refer to the exhibit.

M code snippet:
let
    Source = Sql.Database("Server", "Database"),
    SalesTable = Source{[Schema="dbo",Item="Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(SalesTable, each [Amount] > 100)
in
    #"Filtered Rows"

Refer to the exhibit.

DAX expression:
```
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALL('Date'),
        'Date'[Year] = MAX('Date'[Year])
    )
)
```

Refer to the exhibit.

Power Query M code:
```
let
    Source = Sql.Database("server", "database"),
    Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Sales, each [Year] >= 2020),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ProductID"}, {{"TotalSales", each List.Sum([Amount]), type number}})
in
    #"Grouped Rows"
```

Refer to the exhibit.

DAX expression:
```
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[DateKey], 'Date'[DateKey])
)
```

Refer to the exhibit.

```
{
  "tables": [
    {
      "name": "Sales",
      "columns": [
        {
          "name": "OrderDate",
          "dataType": "dateTime"
        },
        {
          "name": "ProductID",
          "dataType": "int64"
        },
        {
          "name": "Quantity",
          "dataType": "int64"
        },
        {
          "name": "Amount",
          "dataType": "double"
        }
      ]
    },
    {
      "name": "Product",
      "columns": [
        {
          "name": "ProductID",
          "dataType": "int64"
        },
        {
          "name": "ProductName",
          "dataType": "string"
        }
      ]
    }
  ],
  "relationships": [
    {
      "fromTable": "Sales",
      "fromColumn": "ProductID",
      "toTable": "Product",
      "toColumn": "ProductID"
    }
  ]
}
```

Refer to the exhibit.

```
{
  "tables": [
    {
      "name": "Sales",
      "columns": [
        {"name": "Date", "dataType": "dateTime"},
        {"name": "ProductID", "dataType": "int64"},
        {"name": "SalesAmount", "dataType": "double"}
      ],
      "partitions": [
        {
          "name": "CurrentYear",
          "mode": "incrementalRefresh",
          "source": {
            "type": "m",
            "expression": "let ..."
          },
          "refreshPolicy": {
            "policyType": "basic",
            "incrementalGranularity": "day",
            "incrementalPeriods": 365,
            "incrementalPeriodsOffset": 0
          }
        }
      ]
    }
  ]
}
```

Refer to the exhibit.

```
DAX Measure:
Total Sales = 
SUMX(
    FILTER(
        Sales,
        Sales[Quantity] > 10
    ),
    Sales[Amount]
)
```

Refer to the exhibit.
```
{
  "name": "SalesByRegion",
  "source": "SQL Server",
  "tables": [
    {
      "name": "Sales",
      "columns": [
        { "name": "OrderID", "dataType": "int" },
        { "name": "Region", "dataType": "string" },
        { "name": "Amount", "dataType": "decimal" }
      ],
      "partitions": [
        {
          "name": "Partition1",
          "mode": "incrementalRefresh",
          "sourceExpression": "SELECT * FROM Sales WHERE OrderDate >= #\"2024-01-01\"# AND OrderDate < #\"2024-04-01\"#"
        },
        {
          "name": "Partition2",
          "mode": "incrementalRefresh",
          "sourceExpression": "SELECT * FROM Sales WHERE OrderDate >= #\"2024-04-01\"# AND OrderDate < #\"2024-07-01\"#"
        }
      ]
    }
  ]
}
```

Refer to the exhibit.
```
{
  "name": "Date",
  "source": "Power Query",
  "tables": [
    {
      "name": "Date",
      "columns": [
        { "name": "Date", "dataType": "dateTime" },
        { "name": "Year", "dataType": "int" },
        { "name": "Month", "dataType": "int" },
        { "name": "Quarter", "dataType": "int" }
      ],
      "partitions": [
        {
          "name": "DatePartition",
          "mode": "import",
          "sourceExpression": "let
  StartDate = #date(2020,1,1),
  EndDate = #date(2025,12,31),
  NumberOfDays = Duration.Days(EndDate - StartDate),
  Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
  Table = Table.FromList(Dates, Splitter.SplitByNothing())
in
  Table"
        }
      ]
    }
  ]
}
```

Refer to the exhibit.
```
EVALUATE
SUMMARIZECOLUMNS(
    'Product'[Category],
    'Date'[Year],
    "Total Sales", SUMX('Sales', 'Sales'[Quantity] * 'Sales'[UnitPrice]),
    "Filter Context", CALCULATE(COUNTROWS('Sales'), ALL('Product'))
)
```

Refer to the exhibit.
```
{
  "permissions": [
    {
      "dataset": "Sales",
      "user": "user1@contoso.com",
      "role": "Viewer"
    },
    {
      "dataset": "Sales",
      "user": "user2@contoso.com",
      "role": "Reshare"
    }
  ]
}
```

Refer to the exhibit.
```
EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Year],
    'Product'[Category],
    "Total Sales", SUM(Sales[Amount])
)
```

Refer to the exhibit.
```
{
  "rules": [
    {
      "table": "Sales",
      "filter": "Sales[Region] = 'West'"
    },
    {
      "table": "Sales",
      "filter": "Sales[Manager] = USERNAME()"
    },
    {
      "table": "Targets",
      "filter": "Targets[Region] = 'West'"
    }
  ]
}
```

Refer to the exhibit.
```
DEFINE
MEASURE Sales[Total Sales] = SUM(Sales[Amount])
MEASURE Sales[Sales YoY] = 
VAR CurrentSales = [Total Sales]
VAR PreviousSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE(CurrentSales - PreviousSales, PreviousSales)
```

Refer to the exhibit.

```
// DAX Measure
Sales YTD = 
CALCULATE(
    SUM(Sales[Amount]),
    DATESYTD('Date'[Date])
)
```

Refer to the exhibit.

```
// Power Query M
let
    Source = Sql.Database("server", "database"),
    Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Sales, each [OrderDate] >= #date(2023,1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ProductDescription"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"ProductID"}, {{"TotalQty", each List.Sum([Quantity]), type number}})
in
    #"Grouped Rows"
```

Refer to the exhibit.

```
// DAX Calculated Column
Sales[Profit Margin] = 
DIVIDE(
    Sales[Profit],
    Sales[SalesAmount]
)
```

Refer to the exhibit. The following DAX expression is used in a measure:

CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALL(Date),
        Date[Year] = MAX(Date[Year])
    )
)

Refer to the exhibit. The following is a JSON snippet of a Power BI model relationship:

{
  "name": "Sales to Customer",
  "fromTable": "Sales",
  "fromColumn": "CustomerKey",
  "toTable": "Customer",
  "toColumn": "CustomerKey",
  "crossFilteringBehavior": "oneDirection",
  "securityFilteringBehavior": "oneDirection"
}

Refer to the exhibit. The following is a Power Query M code snippet:

let
    Source = Sql.Database("server", "database"),
    Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    FilteredRows = Table.SelectRows(Sales, each [OrderDate] >= #date(2023,1,1)),
    Grouped = Table.Group(FilteredRows, {"CustomerID"}, {{"TotalAmount", each List.Sum([Amount]), type number}})
in
    Grouped

Refer to the exhibit. The following is a DAX measure:

EVALUATE
SUMMARIZECOLUMNS(
    'Product'[Category],
    'Date'[Year],
    "Total Sales", SUM('Sales'[Amount]),
    "Total Cost", SUM('Sales'[Cost])
)
ORDER BY 'Product'[Category], 'Date'[Year]

Refer to the exhibit. The following is a JSON representation of a Power BI model table:

{
  "name": "Sales",
  "columns": [
    {"name": "OrderDate", "dataType": "dateTime"},
    {"name": "Amount", "dataType": "int64"},
    {"name": "Currency", "dataType": "string"}
  ],
  "partitions": [
    {
      "name": "Partition1",
      "source": {
        "type": "m",
        "expression": "let ... in ..."
      }
    }
  ],
  "annotations": [
    {"name": "PBI_Description", "value": "Sales transactions"}
  ]
}