A Self-Contained "Sandbox" PBIX File to Practice With

[mahoneypat]

Hello Community.  This post describes (and provides) a simple model that is useful for learning and/or troubleshooting.  It is a self-contained pbix file (i.e., the whole model is generated with M and DAX, with no external data connections).  This is a small model (<1 Mb) that can be used as a "sandbox" to test stuff out.  Below is a description of what it contains:

Tables

• Product - 6 products in 2 categories (just enough to practice key DAX concepts like ALL(), RELATED(), hierarchies, etc.)
• Sales - random quantities sold on dates from Jan 1, 2018 to Today (with some dates filtered out, so not all days have sales)
• DAX Date table - an example of a useful DAX table that dynamically goes from the start of the year for the min Sales date to the end of year for the max Sales date.  Marked as Date Table, and Sort By Columns set up where needed (Day of Week Name, Month Name, etc.)
• M Date table - this table is not loaded into the model, but shows one way to make a Date table in M (I prefer a DAX one), and is good for testing out date column transformations in M when needed
• _Measures table created with {BLANK()}

Measures

• Some base measures for Sales Count, Total Sales, etc.
• Some example DAX expressions using variants of ALL() on Date or Product table
• Some Time Intelligence examples
• A couple other common patterns (e.g., SUMX with RELATED, getting Total to show correctly)

Relationships

• 1:Many relationships from the Product and Date tables to the Sales table

There is one measure/approach in the model I'd like to call attention to that I think is good for people learning DAX to know about (whether they use this model or not).  I plan a more detailed post soon, but getting your expected results from almost all DAX expressions requires you to understand 3 things:

1. The "table" you start with (filter context from your visual(s))
2. If needed, the "table" you change #1 into w/ CALCULATE() or CALCULATETABLE()
3. The calculation/expression you evaluate on #1 or #2

For Time Intelligence DAX expressions (and Date-related ones in general), you can visualize #2 with the below measure.  It is useful for troubleshooting to confirm you are making the table you meant to make before you even worry about #3.

MinMaxDates =
MIN ( 'Date'[Date] ) & "-"
    & MAX ( 'Date'[Date] )

For example, here it is used in a larger expression (shown below table) to calculate a running total for the current year.  It shows that the table of Date values in the filter part of the CALCULATE() is correct.  Now that we know that, we can now focus on the expression part and replace [MinMaxDates] with the needed expression (a [Total Sales] measure in this case, or something more complicated).  This is a simple example, but this approach might be useful for people just starting out, or for experienced people working on a new way to make a table of Dates for a filter.

Running Total This Year =
CALCULATE (
    [MinMaxDates],
    FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ),
    VALUES ( 'Date'[Year] )
)
// there are easier ways to do running totals but this was a good example for troubleshooting

I use this model sometimes when answering Community Posts, and I hope new Power BI users can use it to practice/see examples and/or experts can use it to demonstrate concepts.  It is easily expandable and adaptable, so feel free to make it better.  There is a lot more that could be done with example measures, and I didn't do anything on visuals, design, etc.  My main focus was to create a self-contained and simple model for people to practice basic DAX concepts

.

Please find the Sandbox.pbix file attached and reply with comments/questions or suggestions for improvement.  Kudos are also appreciated.

Sandbox.pbix

[AllisonKennedy]

Thanks @mahoneypat , great tool and good idea. I'm having a look at your file now, but just a quick question; 

Why do you prefer DAX date table over M? 

I have the opposite preference, so just keen to hear your thoughts and if they might change my mind. 🙂 

[mahoneypat]

Thank you for your comments @AllisonKennedy .  No great reason I choose DAX or M Date tables.  I focused more on DAX (over M) early on, so spent a lot of time on SQLBI.com.  In my opinion, the DAX table is quicker to modify and add new columns.  Also, it's a little easier to make it dynamic on 1 or more Date columns in the model (MIN(Table[Date]) vs. List.Min(Query[Date]).

Regards,

Pat

[Jerid421]

You could / should make this a .pbit, as that is what I think it is intended for? Just a suggestion. I am going to download this though and check it out. It is a very good idea. : )

[mahoneypat]

Thank you for the feedback @Jerid421 .  I originally planned to upload it as a pbit, but when I saw this file was only 1 Mb and the pbit was 800k, I didn't.  I may add a parameter in the future to make it "inflatable" to any size model, so people can use it to test measure performance at scale.  If I do that, I would definitely upload the pbit.