Forum Discussion
Create new table with AddColumns and CrossJoin, but exclude rows with blank values
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:
- Local
- Jan
- 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.
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:
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
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)
5 Replies
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 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)
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
