Skip to main content


Basics of Querying

    Understanding the Foundations Standard SQL is based on the relational model, which is a mathematical model for data management and manipulation. The relational model was initially created and proposed by Edgar F. Codd in 1969. Since then, it has been explained and developed by Chris Date, Hugh Darwen, and others. the true source for the model’s name is the mathematical concept relation. A relation in the relational model is what SQL calls a table. The two are not synonymous. SQL attempts to represent with a table: a relation has a heading and a body. The heading is a set of attributes (what SQL attempts to represent with columns), each of a given type. An attribute is identified by name and type name. The body is a set of tuples (what SQL attempts to represent with rows). Each tuple’s heading is the heading of the relation. Each value of each tuple’s attribute is of its respective type.  Some of the most important principals to understand about SQL stem from the relational

Scenario Based Questions for PL -300 Exam Microsoft Power BI Data Analyst with Solutions and Explanations


You need to address the data before creating the data model. What should you do in Power Query Editor? 

A. Select Column distribution

B. Select the sales_amount column and apply a number filter. 

C. Select Column profile, and then select the sales_amount column. 

D. Transform the sales_amount column to replace negative values with 0.

Answer: C 

Solution: “data concerns” refers to data preview. 

To check the data preview, go to: Power Query Editor> View > Data Preview. In this section you have: 

1. Column distribution: shows unique and distinct values. 

2. Column quality: shows the % of: valid, error and empty values. 

3. Column profile: includes column distribution (not in %) and column quality. Shows statistics values and distribution of the column as well. As data profile is the most complete for addressing concerns, then C is the answer

You are creating a Microsoft Power BI model that has two tables named CityData and Sales. 

 State (Sales)     Type     Sales 

CA                     Internet     60 

CA                     Store         80

TX                    Store         400 

WA                   Internet     150 

WA                   Store         100 

State (CityData)     City        Population (million)

 CA                     Los Angeles      4.00 

CA                     San Francisco      0.90 

New York          New York             8.50 

WA                     Seattle                 0.70 

WA                     Spokane             0.20 

For each of the following statements, select Yes if the statement is true. Otherwise, select No. 

NOTE: Each correct selection is worth one point.

 Statements                                                        Yes                                                         No 

In the Sales table you can write a DAX           

expression that uses the RELATED() function 

to get data from the CityData table.               🔾                                                            🔾

A DAX expression of sales total 

=CALCULATE(SUM(Sales[Sales],All (Sales)) 

will produce the correct total sales value for each 

state based on the data model.                      🔾                                                               🔾

 A table visualization that uses cityData[State] 

and Sales[Sales] will contain sales from the state of TX. 

                                                                                 🔾                                                                 🔾 

Answer: No No No

 Solution: Statement 1: No, because CityData and Sales have a many to many cardinality/relation. The RELATED function cannot be used to fetch a column across a limited relationship. 

What is a limited relationship: A model relationship is limited when there's no guaranteed "one" side, meaning that the relationship uses a Many-to-many cardinality type. 

Statement 2: No, because the DAX expression have an ALL() function, this will always calculate the sum using all the values sales.

Statement 3: No, TX is not a value from the column State (CityData) at the CityData table. 

You create the following step by using Power Query Editor. = Table.ReplaceValue(SalesLT_Address,“1318”,”1319”,Replacer.ReplaceText,{“AddressLine1”})

 A row has a value of 21318 Lasalle Street in the AddressLine1 column. 

What Will the value be when the step is applied? 

A. 1318 

B. 1319 

C. 21318 Lasalle Street

D. 21319 Lasalle Street

Answer: D Solution: Only the value defined as old text (1318) in the M statement, will be replaced by the new text (1319), the rest of the text will remain the same.

You have a report that contains four pages. Each page contains slicer for the same four fields. Users report that when they select values on a slicer on one page, the visuals are not update on all the pages. You need to recommend a solution to ensure that users can select a value once to filter the results on all the pages. What are two possible recommendations to achieve this goal? 

Each correct answer presents a complete solution. 

NOTE: Each correct selection is wroth one point. 

A. Sync the slicers across the pages. 

B. Replace the slicers with page-level filters. 

C. Replace the slicers with visual-level filters. 

D. Create a bookmark for each slicer value. 

E. Replace the slicers with report-level filters. 

Answer: A E Solution: the question refers to a report shared in Power BI Service. However, the filter’s setups could be done from Power BI Desktop as well.

A. By synchronizing the filter, you can manage where you want the filter to be display and which pages you want to filter. For this click on the slicer go to View, Sync slicer and follow the configuration showed aside

B. This option will only filter one page, not the 4 pages. 

C. This option will only filter one visual, not all 4 visuals.

D. This option also solve the problem by creating bookmarks for each selection. Nevertheless, it is not the best option, as it changes totally the filtering experience.

E. This option works, as you can set a report level filter, where users can filter from the filter pane in Power BI Service.  

You have a data model that contains many complex DAX expressions. The expressions contain frequent references to the RELATED and RELATEDTABLE functions. You need to recommend a solution to minimize the use of the RELATED and RELATEDTABLE functions. 
What should you recommend? 

A. Split the model into multiple models.
B. Hide unused columns in the model. 
C. Merge tables by using Power Query. 
D. Transpose. 

Answer: C Solution: Instead of using RELATED functions, is better to transform your data before moving it to Power BI Desktop. By using Power Query, you can merge tables, this will do the exact thing as the RELATED function.


Popular Posts