I have tried to do this before one temp table function at a time, but I think I need to try something different In this video, we will go from the simplest table functions like ROW() to more sophisticated ones like GENERATE, SUMMARIZE, ADDCOLUMNS, NATURALINNERJOIN and a few others. I think that unless you go through several of the table functions in the same video/tutorial, making the logic more complicated with every step, it is hard to compare and contrast those functions as well as being able to understand when and which function makes more sense.
This tutorial starts with a simple sales data set and then we go to DAX Studio where we go through thirteen steps from simple to more complex trying to figure out how to calculate Crazy Sales. What is a Crazy Sale? Well, that is type of an outlier sale where we already had a good week of selling more than $8k of different products and we sold at least four out of our five products that week …AND that single sale of the product that day was at least 20% of the entire week’s sales. So, we are selling a lot more of that product in a day that we should normally be.
The best way to go through the tutorial is to download the PowerBI Desktop File here. Open the Desktop file, launch the DAX Studio, load the Tutorial Scrip (download it here or you can see it at the bottom of this post) and then go through each step in the video and DAX studio at the same time. My believe is that it will definitely help you get a very good understanding of these functions.
DAX Script used in the Tutorial:
--Step 01: ROW() Function
//EVALUATE
//ROW("Result", COUNTROWS(Sales))
--Step 02: VALUES() Function
//EVALUATE
//VALUES('Date'[WeekNo])
--Step 03: SUMMARIZE() Function
//EVALUATE
//SUMMARIZE('Date', 'Date'[WeekNo]) --Note, this is equivalent to Step 02
//
//
--Step 04: SUMMARIZE() Function + calculated field
//EVALUATE
//SUMMARIZE(
// 'Date',
// 'Date'[WeekNo],
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
//)
--Step 05: ADDCOLUMNS() Function
//EVALUATE
//ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
//)
--Step 06: ADDCOLUMNDS() Function across several tables
//EVALUATE
//ADDCOLUMNS(
// Sales,
// "WeekNo",
// RELATED('Date'[WeekNo])
//)
--Step 07: NATURALINNERJOIN() Function
//EVALUATE
//NATURALINNERJOIN(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(
// Sales,
// "WeekNo",
// RELATED('Date'[WeekNo])
// )
//)
//order by [date]
//
//
--Step 08: Test our Filter condition
//DEFINE
// VAR NaturalJoinTable=
// NATURALINNERJOIN(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(
// Sales,
// "WeekNo",
// RELATED('Date'[WeekNo])
// )
// )
//
//EVALUATE
//
//FILTER(
// NaturalJoinTable,
// [Products Sold This Week]>3 &&
// [Weekly Sales]>8000 &&
// DIVIDE([Sales Amount], [Weekly Sales])>0.20
//)
//ORDER BY [Date]
--Step 09: Final Calc
//DEFINE
// VAR NaturalJoinTable=
// NATURALINNERJOIN(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(
// Sales,
// "WeekNo",
// RELATED('Date'[WeekNo])
// )
// )
//
//EVALUATE
//ROW(
// "Result",
// SUMX(
// FILTER(
// NaturalJoinTable,
// [Products Sold This Week]>3 &&
// [Weekly Sales]>8000 &&
// DIVIDE([Sales Amount], [Weekly Sales])>0.20
// ),
// [Sales Amount]
// )
//)
//--Step 10: GENERATE() fUNCTION
//EVALUATE
//GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// VAR CurrentWeekNo = [WeekNo]
//
// RETURN
// ROW(
// "CurrentWeekNo",
// CurrentWeekNo
// )
//)
--Step 10: GENERATE() fUNCTION - point of frustration
//EVALUATE
//GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(Sales, "Matched Sales", [Sales])
//) -- returns 26,341 rows, cartesian join, not what we want
--Step 11: GENERATE() fUNCTION - fixed
//EVALUATE
//GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(RELATEDTABLE(Sales), "Matched Sales", [Sales])
//) -- returns 497 rows - what we want!
//
--Step 12: almost there, check the filter
//EVALUATE
//FILTER(
// GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// RELATEDTABLE(Sales)
// ),
// [Products Sold This Week]>3 &&
// [Weekly Sales]>8000 &&
// DIVIDE([Sales Amount], [Weekly Sales])>0.20
//)
--Step 13: Final Calc yet again
//EVALUATE
//ROW
//(
// "Result",
// SUMX(
// FILTER(
// GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// RELATEDTABLE(Sales)
// ),
// [Products Sold This Week]>3 &&
// [Weekly Sales]>8000 &&
// DIVIDE([Sales Amount], [Weekly Sales])>0.20
// ),
// [Sales Amount]
// )
//)
--Step 01: ROW() Function
//EVALUATE
//ROW("Result", COUNTROWS(Sales))
--Step 02: VALUES() Function
//EVALUATE
//VALUES('Date'[WeekNo])
--Step 03: SUMMARIZE() Function
//EVALUATE
//SUMMARIZE('Date', 'Date'[WeekNo]) --Note, this is equivalent to Step 02
//
//
--Step 04: SUMMARIZE() Function + calculated field
//EVALUATE
//SUMMARIZE(
// 'Date',
// 'Date'[WeekNo],
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
//)
--Step 05: ADDCOLUMNS() Function
//EVALUATE
//ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
//)
--Step 06: ADDCOLUMNDS() Function across several tables
//EVALUATE
//ADDCOLUMNS(
// Sales,
// "WeekNo",
// RELATED('Date'[WeekNo])
//)
--Step 07: NATURALINNERJOIN() Function
//EVALUATE
//NATURALINNERJOIN(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(
// Sales,
// "WeekNo",
// RELATED('Date'[WeekNo])
// )
//)
//order by [date]
//
//
--Step 08: Test our Filter condition
//DEFINE
// VAR NaturalJoinTable=
// NATURALINNERJOIN(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(
// Sales,
// "WeekNo",
// RELATED('Date'[WeekNo])
// )
// )
//
//EVALUATE
//
//FILTER(
// NaturalJoinTable,
// [Products Sold This Week]>3 &&
// [Weekly Sales]>8000 &&
// DIVIDE([Sales Amount], [Weekly Sales])>0.20
//)
//ORDER BY [Date]
--Step 09: Final Calc
//DEFINE
// VAR NaturalJoinTable=
// NATURALINNERJOIN(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(
// Sales,
// "WeekNo",
// RELATED('Date'[WeekNo])
// )
// )
//
//EVALUATE
//ROW(
// "Result",
// SUMX(
// FILTER(
// NaturalJoinTable,
// [Products Sold This Week]>3 &&
// [Weekly Sales]>8000 &&
// DIVIDE([Sales Amount], [Weekly Sales])>0.20
// ),
// [Sales Amount]
// )
//)
//--Step 10: GENERATE() fUNCTION
//EVALUATE
//GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// VAR CurrentWeekNo = [WeekNo]
//
// RETURN
// ROW(
// "CurrentWeekNo",
// CurrentWeekNo
// )
//)
--Step 10: GENERATE() Function- point of frustration
//EVALUATE
//GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(Sales, "Matched Sales", [Sales])
//) -- returns 26,341 rows, cartesian join, not what we want
--Step 11: GENERATE() Function- fixed
//EVALUATE
//GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// ADDCOLUMNS(RELATEDTABLE(Sales), "Matched Sales", [Sales])
//) -- returns 497 rows - what we want!
//
--Step 12: almost there, check the filter
//EVALUATE
//FILTER(
// GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// RELATEDTABLE(Sales)
// ),
// [Products Sold This Week]>3 &&
// [Weekly Sales]>8000 &&
// DIVIDE([Sales Amount], [Weekly Sales])>0.20
//)
--Step 13: Final Calc yet again
//EVALUATE
//ROW
//(
// "Result",
// SUMX(
// FILTER(
// GENERATE(
// ADDCOLUMNS(VALUES('Date'[WeekNo]),
// "Weekly Sales",
// CALCULATE(SUM(Sales[Sales Amount])), -- or you can use {Sales] Measure
// "Products Sold This Week",
// CALCULATE(DISTINCTCOUNT(Sales[ProductID])) --or you can use [# of Products Sold] Measure
// ),
// RELATEDTABLE(Sales)
// ),
// [Products Sold This Week]>3 &&
// [Weekly Sales]>8000 &&
// DIVIDE([Sales Amount], [Weekly Sales])>0.20
// ),
// [Sales Amount]
// )
//)
business intelligist 
Appreciate the high caliber content! Thank you!
You are welcome!
Awesome thanks. Struggling with getting the Iterate through a Snapshot Fact Table The has updated results each month for forecast and actuals. Then for each calendar month calculate actuals by month, forecast by month, cumulative actuals and cumulative forecasts as Estimate for completion (Total of all actuals to date plus remaining forecast to year end) for the snapshot period that corresponds to calendar date.