You need to create a relationship between the Weekly_Returns table and the Date table to meet the reporting requirements of the regional managers. What should you do?
A.
Add the Weekly_Returns data to the Sales table by using RELATED DAX functions.
B.
In the Weekly_Returns table, create a new calculated column named date_id in a format of yyyymmdd and use the calculated column to create a relationship to the Date table.
C.
Create a new table based on the Date table where date_id is unique, and then create a many-to-many relationship to Weekly_Return.
Suggested Answer:B🗳️
Scenario: Regional managers require a visual to analyze weekly sales and returns. To relate the two tables we need a common column.
In the environment eplaination is written "The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww."
So I assume that missing answer is to setup reletionship between above columns
They can't be related on the week columns because in the Date table a week might have two values if it is included in two months (e.g. week 13 in 2021 goes from Mar 29th to Apr 4th) and the relationship will assign the value twice
That would be a many-to-many relationship.
* Date/week has the same item 7 times a week. From February 7th 2022 to February 13th 2022 we are in the same week
* Weekly_Returns/week_id is repeated as many times as regions (sales_regions_id) exist.
I am not absolutely sure how would that work because the description says that there are many data sources and many-to-many relationships have some limitations if some of the tables come from DirectQuery sources.
So, I would vote "B" as correct answer
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships
B is correct.
We can't create a 1:* relationship between Date and weekly_return, because in Date table could be more than one row for a week.(e.g for 2 different Date in same week, then they have same week)
The missing answer should be to create a relationship between Weekly_Return table and Date table because The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. so no need to do what answer B is suggesting.
If many records in Weekly_Returns table have same yyyyww values, the value of new calculated column date_id have same value. For example you return $3000 to Region A on 202102, you also return $2000 to Region B on 202102. After calculation, the date_id of these 2 records are all equal to 20210104.
How to transfer yyyyww to yyyymmdd by calculation?
The argument for the solution doesn't make sense. There is already a relationship between Weekly_returns and Date on date[Week] and Weekly_Returns[Week_ID] as noted.
"The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww." there is no mention of relationship between them, only them being the same
“ The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww.”
Why are you saying there’s no relation?
RELATED() requires relationship between 2 queries. So A can not be correct. If you look at Date table and Weekly returns table, relationship type can not be M2M, So C is as well incorrect. We are left with B, its doable but not a best way. It will need effort.
A is correct (but not the best option as creating a measure is a more preferable option here). Sales relates to Date as n - 1 (sales_date_id - date_id) and Date relates to Weekly_Returns as n - 1 (week - week_id). That means Sales relates to Weekly_Returns as n - 1 also and we can add Weekly return amount to Sales by using the RELATED func
i think it's A
Here we have :The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww
the two columns have also the same type ( same type and same values)
A: incorrect - RELATED DAX function requires a relationship between 2 tables, while we can't build a relationship between sales (daily) & returns (weekly)
B: incorrect - This procedure will change weekly data in returns to daily, and eventually will end up incorrect results after being aggregated to weekly
C: unnecessary & incomplete procedures, hardly to be correct - suppose the relationship between new Date table & Weekly_Returns is built correctly based on week & week_id, and 1 direction from Returns to new Date, but the new Date table is not linked to Sales, it doesn't help with the relationship with Returns & Sales. You still need to link the new Date table with Sales. Also there's no need to create a new Date table as there is one already, it's easier to link Sales & Date (on date_id), Date & Returns (week).
It looks like there is a 'D' option missing here. If not then C, at least it doesn't return incorrect results
correction: B can return correct results after aggregation with weekly Sales. It's not the best modeling approach but still works. C is far from getting the right results. So the answer is B
upvoted 14 times
...
...
This section is not available anymore. Please use the main Exam Page.DA-100 Exam Questions
Log in to ExamTopics
Sign in:
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.
mabianco
Highly Voted 4 years, 1 month agoenrfra
3 years, 10 months agomirzotti
3 years, 4 months agoborinot
3 years, 11 months agoxabieul
3 years, 2 months agolyniguez
Highly Voted 4 years, 1 month agopaulojorge
3 years, 8 months agomanu_94
Most Recent 2 years, 9 months agoMaggieNZ
2 years, 10 months agoAshley090521
3 years, 2 months agoMeyti
3 years, 4 months agoIhueghian
3 years, 6 months agoIhueghian
3 years, 6 months agoCanary_2021
3 years, 10 months agomullered
4 years agoMaue
3 years, 11 months agojakup123
3 years, 11 months agoDeathCaliberKnight
4 years agoMaue
3 years, 11 months agoshankarm
4 years, 1 month agogampy
4 years, 1 month agoAnetaK
4 years, 1 month agoEMNAB
4 years, 1 month agoEllenW
4 years, 1 month agoIvyS
3 years, 7 months agoEllenW
4 years, 1 month ago