Forum Discussion
Summarize Columns - Keeping blank rows
Hi, and apologies in advance if I'm missing something really simple. (Just haven't been able to find a solution elsewhere).
I'm creating a new table by using the SUMMARIZECOLUMNS function and am aiming to attain the number of employees joined per month. Some months have no new employees but I need the new table to still display an empty blank row for that month when this is the case.
I have been able to use the function below successfully, only without the blank rows displaying.
MonthlyJoined = SUMMARIZECOLUMNS('Employee Master'[MonthJoined],"Starters",COUNTROWS('Employee Master'))
Any help greatly appreciated.
2 Replies
Hi lmf232s,
Velarde’s point seems well, but in summarizecolumns function, it has an option [ignore] to keep the blank rows.
Sample:
I create two tables, ‘Product’(ID,Name) and ‘Record’(ProductID,Amount).
‘Product’:
‘Record’:
Then use SUMMARIZECOLUMNS function create a total table:
Table= SUMMARIZECOLUMNS('Product'[Name],"Total",sum(Record[Amount]))Add the ignore option to show all the record.
Table = SUMMARIZECOLUMNS('Product'[Name],"Total",IGNORE(sum(Record[Amount])))I modified your formula and add the ignore option:
MonthlyJoined = SUMMARIZECOLUMNS('Employee Master'[MonthJoined],"Starters",IGNORE ( COUNTROWS('Employee Master')))Reference:
SUMMARIZECOLUMNS Function (DAX)
Regards,
Xiaoxin Sheng
