A Self-Contained “Sandbox” PBIX File to Practice W…

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 (

 

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] 

 

 

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.