Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

Homeschooling report using DAX

As the world continues to live in isolation, parents all over are worried about the education of their children.  Parents are asked to homeschool children, whilst trying to work a full days work from their homes.  This is daunting for anybody.  Are you looking for a fun challenge with a Homeschooling report in Power BI?  Get your kids busy on one of your own developments.  Build a report your kids can interact with.

I built this interactive maths Homeschooling report /study tool for my 7-year-old son.  It is so much more fun than writing answers on paper, which I know he can do well already.  It gets him interacting with the computer, I let him use a mouse and keyboard rather than touch screen to further stimulate his hand-eye coordination while exercising his maths brain.

I realise this is not what Power BI was built for, but it was a lot of fun building, and I’m sure you can use some of these methods in other reports and dashboards at work.

You can interact with the report above or click here to navigate to report.

How to build the Homeschooling report

My aim was to have many options and for the calculations to come up randomly in no particular order.  I also built this homeschooling report completely in Power BI using DAX, it is not connected to any data source.

Set up data structures for the Homeschooling report

Step 1:  Get some base data in a table

I decided I wanted about 100 Additions / Subtractions to be possible on the Homeschooling report, therefore I simply entered a list of numbers 1 -100 in the column.  I did this in excel with its drag function, rather than typing out the numbers, and then copied and pasted it in the Enter data option in Power BI Desktop, and named it Index.

Copy and paste numbers from excel

Step 2: Add random columns

I needed it to be random on refresh, therefore never really showing the same calculation twice, or unlikely to.  To achieve this used the RANDBETWEEN DAX function.

Rand1 = RANDBETWEEN(1,20)

Since I will be adding 2 numbers I had to add another.

Rand2 = RANDBETWEEN(1,100)

More about RANDBETWEEN here.

Step 3:  Add column with calculation to show in report view

Add a column with the calculation the user will see in the Homeschooling report.  This is a simple CONCATENATE DAX function that results in a text format.

Question =
CONCATENATE (
    ‘Addition'[Rand1],
    CONCATENATE ( ” + “CONCATENATE ( ‘Addition'[Rand2], ” = “ ) )
)

Concatenate DAX Addition Calc

 

Step 4:  Add column with answer

Add the answer to the random addition by creating a new measure that simply adds the two numbers.

Answer =
‘Addition'[Rand1] + ‘Addition'[Rand2]

Add column with addition answer

Step 5:  Add table for slicer (List of answers)

The user of the Homeschooling report will have to type the answer to be validated, that answer will be typed in a slicer (filter).  So you need a list of all possible answers, that does not have a relationship with the Addition table.  This was again just added by listing numbers in excel, 1 – 299 in my case, depends on how big your calculation answers might be.  Then copying and pasting it to the Enter data option.  See step 1 for reference.

Relationship view

Design the Homeschooling report

Step 6:  Set up the question

The end result, as seen above, has 1 Addition question appearing on the page.  A “next” option is available to show the next random addition.  This was achieved by using multiple bookmarks, each bookmark is filtered on another number from the index column.

Add a table and link it to the Question.  You will have to filter this to show only 1 at a time.  Using a slicer with index as the filter, activate Single Select.  You should only see 1 addition question at a time.

 

Single Select Filter to show 1 question

Step 7:  Add answers slicer

I use a custom visual from the Microsoft AppSource, called Smart Filter by OKVIZ

 

Smart Filter by OKVIZ

 

Add it to your report and connect the List of answers created in Step 5.

Step 8: DAX Measures to validate answers

These DAX measures are what gives the red and green colours to the correct or incorrect answers.

First, you need to isolate what answer was entered.

Answer = SELECTEDVALUE(‘List of answer'[List of answers])

Then you use that answer to flag whether the answer is correct or incorrect, using an IF function.

Addition Answer check =
IF ( [Answer] = BLANK (), 3, IF ( SUM ( Addition[Answer] ) = [Answer], 1, 0 ) )

Step 9:  Set up conditional formatting for the colour change

I used an image of a cross and a tick mark in a button, and the added conditional formatting to change the Button Fill colour according to the answer chosen.

Conditional Formatting for Correct Answer

Conditional Formatting for correct answer

Conditional Formatting for Incorrect Answer

Conditional Formatting for Incorrect answer

You then have this effect

Example of correct and incorrect answers

The full report then consists of the following:

 

Diagram with all visual on report

The rest of the report came together by using bookmarks and layering of buttons.

I can go deeper into Bookmarks at another stage, leave me a message if you would like to see a post on it.  In a nutshell, each bookmark is filtered on a different index number, revealing a new addition question.  I did 20 bookmarks and then looped through them.  I just hid the slicer from the view.

For a very quick explanation on what bookmarks does here is a short video and for a great blog with great ideas click here.

From this point on you can add your own style to the report using a beautiful background, changing the pictures and buttons used for the navigation etc.

The smart thing about using the RANDBETWEEN DAX function is that every time you refresh the report it shuffles the numbers, therefore Index = 1 has a different question appearing.  Therefore you can set your report to refresh automatically which will ensure that the order of the list of numbers will hardly ever be the same. Please note that you will not be able to set an automatic refresh if the report is not linked to data table, therefore you will have to link the base data in Step 1 to a CSV or Excel sheet that lists the numbers, however if you just want to manually refresh now and again use steps above.

Repeat all above steps to add Subtraction to the report.

Get some great Power BI Training with us, enrol here.

Leave a Comment

Your email address will not be published. Required fields are marked *