PL-300: Microsoft Power BI Data Analyst
You are creating a work schedule for a retail store.
You have the following data from a query named Schedule.
You need to visualize the data as shown in the following exhibit.
You add a matrix visualization, and then you add Employee to the rows and Scheduled to columns.
Which DAX formula should you use to create the measure that will display the checkboxes?
Answer is Schedule Display = IF(COUNTROWS(Schedule) > 0, UNICHAR(9635)," ")
COUNTX(table,expression)
COUNTAX(table,expression)
COUNTROWS(table)
COUNTA(column)
COUNT(column)
You have a property named FactInternetSales used by several Power BI reports. The query is shown in the exhibit.
You plan to create a bar chart showing the count of sales by year that have a SalesAmount greater than $1,000.
You need to create a measure that will be used in the bar chart.
How should you complete the DAX formula?
Answer is COUNTROWS and FILTER
COUNTROWS(FILTER('Europe','Europe'[City]= "UK - London"))
Alternate:
COUNTX(Europe,FILTER(Europe,Europe[City] = "UK - London"))
You have a Power BI model for sales data. You create a measure to calculate the year-to-date sales.
You need to compare the year-to-date sales with the previous year for the same time period.
Which DAX function should you use?
LASTDATE
TOTALYTD
SAMEPERIODLASTYEAR
PREVIOUSYEAR
DATEADD
F. DATESVTD
Answer is SAMEPERIODLASTYEAR
1st already existing measure to calculate ytd sales:
YtdSales = CALCULATE(SUM(FactInternetSales[SalesAmount]), DATESYTD(FactInternetSales[DueDate]))
To be able to compare you need to create a 2nd measure to calculate ytd sales same period previous year:
YtdSalesSamePeriodLastYear = CALCULATE([YtdSales], SAMEPERIODLASTYEAR(FactInternetSales[DueDate]))
You have a Power BI model that contains tables named Sales and Date. Sales contains four columns named SalesAmount, OrderDate, SalesPerson, and OrderID.
You need to create a measure to calculate the last 12 months of sales. You must start from the last date a sale was made and ignore any filters set on the report.
How should you complete the DAX formula?
Answer is DATEADD and LASTNONBLANK
1. get the date 12 months ago: DateAdd
2. get the valid last date: LastNonBlank
3. nested above 2 together by “Calculate”
Reference:
https://www.kasperonbi.com/show-only-the-sales-for-the-last-12-months/
https://docs.microsoft.com/en-us/dax/dateadd-function-dax
https://docs.microsoft.com/en-us/dax/lastnonblank-function-dax
https://docs.microsoft.com/en-us/dax/calculate-function-dax
You have a Power BI model that contains a table named Person. Person contains a whole number column named Age.
You need to write a DAX measure that finds the middle value in the range of Age values.
Which two formulas should you use? Each answer presents a complete solution.
MEDIAN(‘Person’[Age])
PERCENTILE.INC((‘Person’[Age], 0.5)
AVERAGE(‘Person’[Age])
RANK.EQ(‘Person’[Age], ‘Person’[Age])
You have a Power BI model that contains the following two tables:
- Assets (AssetID, AssetName, Purchase_DateID, Value)
- Date (DateID, Date, Month, Week, Year)
The tables have a relationship. Date is marked as a date table in the Power BI model.
You need to create a measure to calculate the percentage that the total assets value increased since one year ago.
Which DAX formula should you use?
(sum(Assets[Value]) – CALCULATE(sum(Assets[Value]),SAMEPERIODLASTYEAR(‘Date’[Date])))/CALCULATE(sum(Assets[Value]),SAMEPERIODLASTYEAR (‘Date’ [Date]))
CALCULATE(sum(Assets[Value]), SAMEPERIODLASTYEAR("˜Date'[Date]))/ (sum(Assets[Value])
CALCULATE(sum(Assets[Value]),DATESYTD(("˜Date'[Date]))/ (sum(Assets[Value])
sum(Assets[Value]) / CALCULATE(sum(Assets[Value]), SAMEPERIODLASTYEAR('Date'[Date]))
Answer is (sum(Assets[Value]) – CALCULATE(sum(Assets[Value]),SAMEPERIODLASTYEAR(‘Date’[Date])))/CALCULATE(sum(Assets[Value]),SAMEPERIODLASTYEAR (‘Date’ [Date]))
basis formula is % = (current/prior - 1) = (current - prior) / prior
You need to write a DAX measure that returns the revenue for selected dates during the previous year.
Which two DAX functions should you include in the measure? Each correct answer presents part of the solution.
CALCULATE
CLOSINGBALANCEYEAR
PREVIOUSQUARTER
SAMEPERIODLASTYEAR
Answers are;
CALCULATE
SAMEPERIODLASTYEAR
You need to create a role-playing dimension using DAX. Which type of DAX expression should you use?
Calculated column
Calculated table
Measure
What-If analysis
Answer is Calculated table
What benefit do you get from analyzing metadata?
The benefit of analyzing metadata is that you can clearly identify data inconsistencies with your dataset.
The benefit of analyzing the metadata is to get familiar with your data.
The benefit of analyzing the metadata is to know the number of rows, columns and tables being loaded into your model.
Answer is The benefit of analyzing metadata is that you can clearly identify data inconsistencies with your dataset.
Which tool enables you to identify bottlenecks that exist in code?
Q&A.
Column profiling.
Performance analyzer.
Answer is Performance analyzer.