Forum Discussion

Spencer's avatar
Spencer
Helper II
10 years ago
Solved

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 ...
Need Help
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    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))
Anonymous's avatar
Anonymous
Not applicable
10 years ago

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