Comprehensive DAX Table Functions Tutorial (GENERATE, SUMMARIZE, ADDCOLUMNS, NATURALINNERJOIN and more… )

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]
//	)
//)
	
	
	

2 thoughts on “Comprehensive DAX Table Functions Tutorial (GENERATE, SUMMARIZE, ADDCOLUMNS, NATURALINNERJOIN and more… )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s