DAX.do quiz
PRACTICE DAX ON THE WEBSITE DAX.DO
…using practice questions for the Contoso model to exercise writing DAX measures and queries.
DAX.DO - LEARN DAX BY DOING IT
Practicing with fake data will help you make better solutions with your real data.
Learning anything requires both using theoretical material like books and courses as well as regular practice. Getting hands-on experience with things you learn helps you apply your knowledge and test assumptions. It also improves other, general skills like critical thinking, problem-solving and knowledge about common business processes.
This is true for learning data modelling, visualisation and DAX in Power BI, as well. For example, you can complete weekly Workout Wednesday exercises to create a new visual every week. You can also practice more freeform with personal data projects. However, sometimes it is better to have a more direct test of your knowledge. Practice questions using common or shared models are a good way to do this. However, it isn’t always easy to find sample models, and it’s cumbersome to share data or .pbix files.
dax.do is a website created by sqlbi to practice DAX on two different data models from within the browser. Using dax.do, you can write and share DAX queries on the sample models with no extra effort required. This is an invaluable tool for testing different approaches, but also can be an excellent vehicle to deliver practice questions. I recently tried this for myself through Twitter, where I shared two practice questions. The winners - the first ones to share correct answers and queries - won a free Data Goblins adventure map. This was a fun exercise, so I decided to make it a regular thing. The exact frequency of these exercises is yet to be decided, but at least once every two weeks.
https://t.co/EQ0en2KwPE Q2: "Companies that place 5+ orders get a special rebate if we deliver to them >1 workday from due date (early or late), on avg. How many customers are eligible for this rebate? Which customer overall has highest avg # workdays b/w due & delivery date?" https://t.co/ou4I8E0WXN
— Kurt Buhler (@kurtbuhler) May 21, 2023
This is a visual summary of the discussion, I think.
— Kurt Buhler (@kurtbuhler) May 22, 2023
This is interesting because the "correct" answer in the mind of the business stakeholder may in fact be a flawed approach.
If the goal is improve delivery times and due-delivery variance > 1 is bad, your approach is better. pic.twitter.com/AdSSwPsBzO
In this article I share 3 DAX practice questions for dax.do. Each question is incrementally harder than the previous one. They are designed for people learning tabular modelling who have already familiarised themselves with the basics of DAX.
The first person to post in the comments of this article the correct answer and a link to a correct dax.do query will win this Data Goblins ‘source control’ mug. I’ll send it to you for free, but I can’t pre-pay duties/import fees. You can bill me for that.
First person to post in the comments the correct answers and a link to a correct dax.do query wins this mug.
INSTRUCTIONS
Go to dax.do.
Read the below questions.
Use the information provided and the Contoso model to answer the questions.
I will not be able to provide clarifying information or answer questions about the below questions.When ready, save your query (top-left of dax.do) and copy the link.
Post the answer to the questions and a link to your dax.do query in the comments, below.
If your answer is correct, I will contact you in a week to ask you to e-mail me your information so I can send you the mug.
I will post a correct dax.do query at the bottom of the article when I post the next quiz question.
Note: My focus is providing interesting and challenging questions that focus on a business problem. It is possible that for the harder questions there are more than one answer depending on how you interpret the requirements or the business logic.
QUIZ #002
QUESTION 1
You get the 'Contoso' model, which was built by someone else. The business asks you whether the measures [Margin] and [Margin %] are the "Gross Margin" or "Standard (Std.) Margin".
The calculation for the Gross Margin is as follows:
Gross Sales = Total Units Sold * Unit Price
COGS = Total Units Sold * Unit Cost
Gross Margin = Gross Sales - COGS
Gross Margin Perc. = Gross Margin / Gross Sales
The calculation for Std. Margin is as follows:
Discount = Total Units Sold * Unit Discount
Net Sales = Gross Sales - Discount
Std. Margin = Net Sales - COGS
Std. Margin Perc. = Std. Margin / Net Sales
a) Do the margin measures correspond to the Gross Margin or Std. Margin?
QUESTION 2
The business say that the Margin values are too high. After investigation, they realize the "Unit Cost" does not include indirect costs of distribution. This cost is a percentage based on the weight of the product
If the product is a "Download Game" subcategory, the Distribution Cost is 15.00 / Unit.
The below CSV shows the distribution cost:
Weight,DistributionUnitCost
< 1 KG, 5.00
1-2 KG, 10.00
3-5 KG, 15.00
6-10 KG, 22.50,
11-25 KG, 33.50,
26-100 KG, 50.0,
101 KG+, 100
a) How many products fall in each of the above "Distribution Cost" categories? Ensure you account for any nulls.
It will help you to know that:
There are 35.27 ounces in a kilogram
There are 2.2 kilograms in a pound
There are 1000 grams in a kilogram
b) Do you think that there are any data quality issues with the weight data? (Yes or No). If yes, which Product categories or subcategories have data quality issues with weight?
QUESTION 3
Calculate the [Corrected Std. Margin Perc.] using the measures from (1) and the below:
Corrected Std. Margin:
Distribution Cost = Total Units Sold = Distribution Unit Cost (see 2)
Corrected Std. Margin = Net Sales - COGS - Distribution Cost
Corrected Std. Margin (%) = Corrected Std. Margin / Net Sales
Filter out any products that should have a weight but don't.
a) Which product brand has the biggest difference between the corrected std. margin and the original?
b) Approximately how big is the difference, rounded to the nearest percent?
TO CONCLUDE
Practicing is an important part of learning. I recently have been having fun thinking of practice questions for dax.do. For a while, I will regularly post and share quiz questions you can try to practice DAX. The first person to get a correct answer wins a prize!
