Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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))
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
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |