Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I've been trying to figure this one out but its proving to be a bit tough...
What I need to do in DAX is a sum of the "Value" column only when the CHECK1 and CHECK2 columns for that employee are set to 0 in all the other rows for the selected Date range.
This is an example dataset:
EMPLOYEE ID | VALUE | CHECK | CHECK2 | DATE |
1 | 1 | 0 | 0 | 01/01/2019 |
1 | 1 | 0 | 0 | 02/01/2019 |
1 | 1 | 0 | 0 | 03/01/2019 |
1 | 1 | 0 | 0 | 04/01/2019 |
1 | 1 | 0 | 0 | 05/01/2019 |
1 | 1 | 0 | 0 | 06/01/2019 |
2 | 1 | 1 | 0 | 03/01/2019 |
2 | 1 | 0 | 0 | 04/01/2019 |
2 | 1 | 0 | 0 | 05/01/2019 |
2 | 1 | 0 | 0 | 06/01/2019 |
3 | 1 | 0 | 0 | 01/06/2019 |
3 | 1 | 0 | 1 | 02/06/2019 |
Thanks,
if I can provide any more info or explain the issue any further I'd be glad to do so
Solved! Go to Solution.
Hi @ixtilion,
I got this code to work on your sample data
Measure = VAR _t = ADDCOLUMNS ( VALUES ( 'Table'[EMPLOYEE ID] ); "c1"; CALCULATE ( SUM ( 'Table'[CHECK] ) ); "c2"; CALCULATE ( SUM ( 'Table'[CHECK2] ) ) ) VAR _tt = CALCULATETABLE ( VALUES ( 'Table'[EMPLOYEE ID] ); FILTER ( 'Table'; ( 'Table'[EMPLOYEE ID]; 0; 0 ) IN _t ) ) RETURN CALCULATE ( SUM ( 'Table'[VALUE] ); FILTER ( 'Table'; ( 'Table'[EMPLOYEE ID] ) IN _tt ) )
Regards,
S
Hi @ixtilion,
I got this code to work on your sample data
Measure = VAR _t = ADDCOLUMNS ( VALUES ( 'Table'[EMPLOYEE ID] ); "c1"; CALCULATE ( SUM ( 'Table'[CHECK] ) ); "c2"; CALCULATE ( SUM ( 'Table'[CHECK2] ) ) ) VAR _tt = CALCULATETABLE ( VALUES ( 'Table'[EMPLOYEE ID] ); FILTER ( 'Table'; ( 'Table'[EMPLOYEE ID]; 0; 0 ) IN _t ) ) RETURN CALCULATE ( SUM ( 'Table'[VALUE] ); FILTER ( 'Table'; ( 'Table'[EMPLOYEE ID] ) IN _tt ) )
Regards,
S
@sturlaws thanks a lot, it works perfectly and I learned something about using variables and tables in DAX expressions!
Im going to try it out in a real enviroment with a lot of rows and see how it performs, thanks for the insight 🙂
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |