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

[JRHans09]

I am trying to create a budget table based on sales values from the previous year, but would like to exclude any rows that might have blank Annual Budget values for the current year because the particular combination of the joined rows might not have had sales in the previous year.

Is it possible to use ADDCOLUMNS and CROSSJOIN to create a new table, but excludes rows in that new table based on the results of the CALCULATE function?

See here:

Annual Budget = 
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
		)
	)
)

And see sample results:

Annual Budget	Full Name	Job Type1	Month MMM	Year
	Tom	Local	Jan	2020
35,000	Dave	Local	Jan	2020
50,000	Sherry	Local	Jan	2020
	Alice	Local	Jan	2020
45,000	Talia	Local	Jan	2020

If neither Tom nor Alice had sales for:

1. Local
2. Jan
3. 2019 (table above shows 2020, but budget should pull data from 2019)

Is it possible to exclude those rows in the new table?

Thanks in advance.

[d_gosbell · Accepted solution]

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

[v-lili6-msft · Accepted solution]

hi  @JRHans09 

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

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

        )

    )

) ,[Annual Budget]<>BLANK())

Regards,

Lin

[v-lili6-msft · Accepted solution]

hi  @JRHans09 

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

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

        )

    )

) ,[Annual Budget]<>BLANK())

Regards,

Lin

[d_gosbell · Accepted solution]

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

[JRHans09]

@d_gosbell - thanks, your answer is virtually the same.

[d_gosbell · Accepted solution]

---

@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)

[JRHans09]

@d_gosbell - thank you for your clarification. It is helpful. I will apply the logic to my case.