Explanation:

Box 1: SUMMARIZE

SUMMARIZE

Returns a summary table for the requested totals over a set of groups.

Syntax

SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>] …)

Note: EVALUATE is a DAX statement that is needed to execute a query. EVALUATE followed by any table expression returns the result of the table expression. Moreover, one or more EVALUATE statements can be preceded by special definitions like local tables, columns, measures, and variables that have the scope of the entire batch of EVALUATE statements executed together.

— Definition section

[DEFINE { MEASURE <tableName>[<name>] = <expression> }

{ COLUMN <tableName>[<name>] = <expression> } { TABLE <tableName> = <expression> }

{ VAR <name> = <expression>}]

— Query expression

EVALUATE <table>

— Result modifiers

[ORDER BY {<expression> [{ASC | DESC}]}[, …]

[START AT {<value>|<parameter>} [, …]]]

Note 2: Example

SAMEPERIODLASTYEAR

Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.

Syntax

SAMEPERIODLASTYEAR(<dates>)

Parameters

dates

A column containing dates.

Return value

A single-column table of date values.

The following sample formula creates a measure that calculates the previous year sales of Reseller sales.

= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime [DateKey]))

Box 2: _LYSales

Just return the variable.

Note: You can define a variable in any DAX expression by using VAR followed by RETURN. In one or several VAR sections, you individually declare the variables needed to compute the expression; in the RETURN part you provide the expression itself.

Incorrect:

* FILTER

Returns a table that represents a subset of another table or expression.

Syntax

FILTER(<table>,<filter>)

table

The table to be filtered. The table can also be an expression that results in a table.

Filter

A Boolean expression that is to be evaluated for each row of the table. For example, [Amount] > 0 or [Region] = "France"

* SUMMARIZECOLUMNS

Returns a summary table over a set of groups.

Syntax

SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…, [<filterTable>]…[, <name>, <expression>]…)

* CALCULATETABLE

Evaluates a table expression in a modified filter context.

Note

There’s also the CALCULATE function. It performs exactly the same functionality, except it modifies the filter context applied to an expression that returns a scalar value.

Syntax

CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])

Reference: https://www.sqlbi.com/articles/variables-in-dax

https://dax.guide/st/evaluate/

https://learn.microsoft.com/en-us/dax/summarize-function-dax

Explanation:

Box 1: DATETRUNC

QUESTION NO: NO: PeriodDate: Returns a date representing the first day of the month for OrderDate

DATETRUNC (Transact-SQL)

The DATETRUNC function returns an input date truncated to a specified datepart.

Syntax

DATETRUNC (datepart, date)

Arguments

datepart

Specifies the precision for truncation. This table lists all the valid datepart values for DATETRUNC, given that it’s also a valid part of the input date type.

Box 2: weekday

Question: DayName returns the name of the day for OrderDate, such as Wednesday

Note: DATENAME (Transact-SQL)

This function returns a character string representing the specified datepart of the specified date.

Syntax

DATENAME (datepart, date)

datepart

The specific part of the date argument that DATENAME will return. This table lists all valid datepart arguments.

weekday

Etc.

Incorrect:

* DATE_BUCKET (Transact-SQL)

This function returns the date-time value corresponding to the start of each date-time bucket from the timestamp defined by the origin parameter, or the default origin value of 1900-01-01 00:00:00.000 if the origin parameter isn’t specified.

See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQL date and time data types and functions.

Syntax

DATE_BUCKET (datepart, number, date [, origin ] )

* DATEFROMPARTS

This function returns a date value that maps to the specified year, month, and day values.

Syntax

DATEFROMPARTS (year, month, day )

* DATEPART (Transact-SQL)

This function returns an integer representing the specified datepart of the specified date.

Reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/datetrunc-transact-sql

Explanation:

Box 1: Yes

PySpark Select Columns From DataFrame

In PySpark, select() function is used to select single, multiple, column by index, all columns from the list and the nested columns from a DataFrame, PySpark select() is a transformation function hence it returns a new DataFrame with the selected columns.

Select Single & Multiple Columns From PySpark

You can select the single or multiple columns of the DataFrame by passing the column names you wanted to select to the select() function. Since DataFrame is immutable, this creates a new DataFrame with selected columns. show() function is used to show the Dataframe contents.

Box 2: No

Box 3: Yes

pyspark.sql.DataFrameReader.csv

Loads a CSV file and returns the result as a DataFrame.

This function will go through the input once to determine the input schema if inferSchema is enabled. To avoid going through the entire data once, disable inferSchema option or specify the schema explicitly using schema.

Note: pyspark.sql.DataFrameWriter.saveAsTable

Saves the content of the DataFrame as the specified table.

Reference:

https://sparkbyexamples.com/pyspark/select-columns-from-pyspark-dataframe/

https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.DataFrameReader.csv.html

Explanation:

Box 1: Delta

With Microsoft OneLake integration for semantic models, data imported into model tables can also be automatically written to Delta tables in OneLake. The Delta format is the unified table format across all compute engines in Microsoft Fabric. OneLake integration exports the data with all key performance features enabled to provide more seamless data access with higher performance.

Data scientists, database analysts, app developers, data engineers, and other data consumers can then access the same data that drives your business intelligence and financial reports in Power BI. T-SQL, Python, Scala, PySpark, Spark SQL, R, and no-code/low-code solutions can all be used to query data from Delta tables.

Box 2: Tables/productline1

How to Save a Pyspark Dataframe as a Table in a Fabric Warehouse

Example, save the dataframe result as a table in my Lakehouse.:

tf_df.write.format("delta").mode("append").save("Tables/actual_weather")

Scenario: Data Preparation Requirements

Contoso identifies the following data preparation requirements:

* The Research division data for Productline1 must be retrieved from Lakehouse1 by using Fabric notebooks.

* All the Research division data in the lakehouses must be presented as managed tables in Lakehouse explorer.

Reference:

https://learn.microsoft.com/en-us/power-bi/enterprise/onelake-integration-overview

https://medium.com/the-data-therapy/how-to-save-a-pyspark-dataframe-as-a-table-in-a-fabric-warehouse-e3b04915f066

Explanation: