exam questions

Exam PL-300 All Questions

View all questions & answers for the PL-300 exam

Exam PL-300 topic 2 question 78 discussion

Actual exam question from Microsoft's PL-300
Question #: 78
Topic #: 2
[All PL-300 Questions]

HOTSPOT
-

You are creating a Power BI report that will show the number of current employees over time. The report will use Import storage mode for all tables.

The employment data will be imported from Azure SQL Database in a monthly snapshot. The data will be stored in a table named Headcount and will contain the following:

• One row per employee for each month the employee is employed
• In each row, a date key that shows the first day of the month of each snapshot

You have a related date table that contains dates for the years 2020 to 2030.

You need to create a semi-additive DAX measure that will return the count of employees for the last available date in a year, quarter, or month.

How should you complete the measure? To answer, select the appropriate options in the answer area.

Show Suggested Answer Hide Answer
Suggested Answer:

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
aloysiusjw
Highly Voted 1 year, 3 months ago
LASTNONBLANK doesn't work as it requires 2 inputs LastDate('headcount'[datekey])/ FirstDate('headcount'[datekey]) doesn't work simply because firstdate and lastdate requires a date. Datekey is not a date Therefore, Lastdate('date'[date]) is correct
upvoted 31 times
...
RicoPallazzo7
Highly Voted 1 year, 4 months ago
CountRows("HeadCount") LastDate(headcount["DateKey"]) Why? The first to count the number of employes The second because if we use lastDate over Date it will retrieve always the 31 december 2030 (It says that in date table are present dates from 2020 to 2030) So the result will always be the same. But if we take the lastDate in the DateKey we'll have the first day of the month and year of the last snapshot. Am i correct?
upvoted 18 times
SylUK
1 month, 2 weeks ago
Please have a look in the syntax of the function LASDATE in DAX which is : LASTDATE(<dates>). Dates represent the column in which there is date . Therefore the given answer is correct: CountRows("HeadCount") for the first and LastDate('Date'[Date])) for the second.
upvoted 1 times
...
as198184
6 months, 3 weeks ago
You are right Rico. Tried it in power bi and it works perfectly
upvoted 2 times
...
Mo2011
9 months, 3 weeks ago
No, the LASTDATE('Date'[Date]) function does not always return the latest date in the entire range of the Date table (which would be 12/31/2030 in this case). Instead, it returns the latest date within the current filter context. This means it considers the dates that are relevant to the current context of your report, visualization, or calculation.
upvoted 2 times
...
Alscoran
1 year, 3 months ago
I don't think so. Every employee has an entry for EVERY month they are employed. So counting the rows in that table will be too high.
upvoted 4 times
sarraEB
1 year, 1 month ago
Yes but in this case, we will use the calculate function that evaluates an expression in a modified filter context. Here the filter is on the dates. It will give us the last available date in a year, quarter, or month.: Lastdate('date'[date]). So you will have only one date for each employee=> one row for each employee.
upvoted 2 times
...
...
...
5bf040d
Most Recent 3 days, 6 hours ago
CountRows("HeadCount") : return the count of employees LastDate(headcount["DateKey"]): obtain the last date available, the exercise mention you have a related date table, so, the data table is connected to Headcount table via DateKey
upvoted 1 times
...
freudn
4 months ago
CountRows("HeadCount") LastDate(headcount["DateKey"]) it's literally an almost identical example in the Microsoft documentation: quote: " Example The following sample formula creates a measure that obtains the last date, for the current context, when a sale was made in the Internet sales channel. = LASTDATE('InternetSales_USD'[SaleDateKey]) " https://learn.microsoft.com/en-us/dax/lastdate-function-dax#example
upvoted 1 times
...
8b7b763
5 months, 1 week ago
EmployeeCount = CALCULATE( APPROXIMATEDISTINCTCOUNT(Headcount[EmployeeID]), LASTDATE('Date'[Date]) )
upvoted 3 times
...
0e18c76
10 months, 1 week ago
The answer seems correct.
upvoted 3 times
...
Abhi_1526
1 year ago
There is no much upvotes.Feels like everyone in tired of solving these questions.
upvoted 8 times
...
yuri_rusanov
1 year, 3 months ago
Table Headcount will contain “one row per employee for each month the employee is employed”. As I understand it, if we run the newly created measure for quarter or year, then the table will contain multiple rows of the same employee, given that the employee was employed the whole time. Therefore using CountRows(‘’Headcount) would retrieve 3 or 12 for each employee being present in the whole period, which seems problematic as he is only one person. On the other hand, ApproximateDistinctCount will count an instance of that same employee only once, since the function aggregates on the passed column (key) before counting. Correct me if I am wrong, but I think the correct answer should be ApproximateDistinctCount and LastDate(‘Date’[Date]) Reference: https://learn.microsoft.com/en-us/dax/approximate-distinctcount-function-dax
upvoted 9 times
Dmytro_UA
1 year, 2 months ago
Unfortunately, you are wrong. "This function requires DirectQuery mode" (https://learn.microsoft.com/en-us/dax/approximate-distinctcount-function-dax). In our case "The report will use Import storage mode for all tables" . So the only one solution - COUNTROWS and LastDate('Date'[Date]).
upvoted 9 times
...
Helena1
1 year, 3 months ago
I agree. I thought of the same thing. I wonder if anyone has a definitive answer to help us here.
upvoted 2 times
...
Alscoran
1 year, 3 months ago
I agree with you. Countrows will not work because of what you have pointed out.
upvoted 3 times
...
...
Ryan_042
1 year, 4 months ago
CALCULATE( COUNTROWS('Headcount'), LASTNONBLANK('Headcount'[Date]) ) The problem is the table "Headcount"'s date key is the first day of each month. LASTDATE('DATE'[DATE]) returns the last day of the selected period, which filters out the value in the "Headcount" table.
upvoted 5 times
aloysiusjw
1 year, 3 months ago
LASTNONBLANK doesn't work as it requires 2 inputs LastDate('headcount'[datekey])/ FirstDate('headcount'[datekey]) doesn't work simply because firstdate and lastdate requires a date. Datekey is not a date Therefore, Lastdate('date'[date]) is correct
upvoted 3 times
...
...
ZillowGosia
1 year, 4 months ago
Chat GTP LastDateHeadcount := VAR LastDate = MAXX( FILTER( ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]) && 'Date'[Year] = MAX('Date'[Year]) ), 'Date'[Date] ) RETURN CALCULATE( COUNTROWS('Headcount'), 'Headcount'[DateKey] = LastDate ) So Chat GTP also suggest: CountRows("HeadCount") LastDate(headcount["DateKey"])
upvoted 3 times
...
srujji
1 year, 5 months ago
CORRECT
upvoted 4 times
...
tranquanghuy2111
1 year, 5 months ago
while the input is the first day of month, but the request is the last date. How can we set the filter as lastdate? i testes but it gave blank()
upvoted 3 times
tranquanghuy2111
1 year, 5 months ago
LASTNONBLANK requests two argument while in the question only show one agurment
upvoted 1 times
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
Loading ...
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago