Summarize Columns - Keeping blank rows

[Spencer]

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.

[v-shex-msft]

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('Employe​e Master')))

Reference:

SUMMARIZECOLUMNS Function (DAX)

Regards,

Xiaoxin Sheng

[Vvelarde · Accepted solution]

hi @Spencer

1. Create a Table with Months and related to Employee Master with Month Joined Column.

2. Create a new table with this Dax:

Monthly = SUMMARIZECOLUMNS(Months[Month];"Starters";If(COUNTROWS(RELATEDTABLE(Employee Master))>0;COUNTROWS(RELATEDTABLE(Employee Master));0))