Create new table with AddColumns and CrossJoin, but exclude rows with blank values

This should be possible, in theory you should be able to wrap your existing expression in FILTER()

eg.

Annual Budget = 
FILTER(
ADDCOLUMNS(
	FILTER(
		CROSSJOIN(
			DISTINCT( Dates[Month MMM] ),
			DISTINCT( Dates[Year] ),
			VALUES( SalesRep[Full Name] ),
			VALUES( JobType[JobType1] )
		),
		Dates[Year] = MAX( Dates[Year] ) //to always keep current year
	),
	"Annual Budget",
	CALCULATE(
		[Total Sales],
		DATEADD(
			Dates[Date],
			-1,
			YEAR
		)
	)
),
NOT( ISBLANK( [Annual Budget] ) )
)

hi  JRHans09 

You could add a FILTER in the for mula as below:

Regards,

Lin

---

JRHans09 wrote:

d_gosbell - thanks, your answer is virtually the same.

---

But they are not exactly the same. I am using NOT( ISBLANK( ... ) ) as it will ONLY exclude blanks. if you do <> BLANK() it will exclude blanks and 0 values (since blanks coalesce to a numeric value of 0 when you compare them to a numeric measure or column)