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.
Here is the Excel formula is question:
=SUMPRODUCT(--(YEAR(TrackingSheet!$E$5:$E$133)=YEAR(C23)),--(TrackingSheet!$E$5:$E$133<>""),--(TrackingSheet!$H$5:$H$133="Yes"),--(TrackingSheet!$G$5:$G$133<>"Relapsing"),--(MONTH(TrackingSheet!$E$5:$E$133)=MONTH(C23)))
The problem is that I am not familiar with the SUMPRODUCT formular and the double-negative signs (or their purpose). If someone could be so kind as to explain the structure of the formula to me and the purpose of the double-negative, I would appreciate it for converting future formulas to DAX.
Thanks 🙂
Solved! Go to Solution.
-- turns logical values into numbers. --(TRUE) = 1, --(FALSE) = 0.
=SUMPRODUCT( -- ( YEAR( TrackingSheet!$E$5:$E$133 ) = YEAR( C23 ) ), -- ( TrackingSheet!$E$5:$E$133 <> "" ), -- ( TrackingSheet!$H$5:$H$133 = "Yes" ), -- ( TrackingSheet!$G$5:$G$133 <> "Relapsing" ), -- ( MONTH( TrackingSheet!$E$5:$E$133 ) = MONTH( C23 ) ) )
Basically, the formula says:
SUMPRODUCT then multiplies the columns of logicals (turned into 1 and 0) row by row and then sums it up. Thus you get a count of rows where the above conditions are all TRUE.
Best
Darek
-- turns logical values into numbers. --(TRUE) = 1, --(FALSE) = 0.
=SUMPRODUCT( -- ( YEAR( TrackingSheet!$E$5:$E$133 ) = YEAR( C23 ) ), -- ( TrackingSheet!$E$5:$E$133 <> "" ), -- ( TrackingSheet!$H$5:$H$133 = "Yes" ), -- ( TrackingSheet!$G$5:$G$133 <> "Relapsing" ), -- ( MONTH( TrackingSheet!$E$5:$E$133 ) = MONTH( C23 ) ) )
Basically, the formula says:
SUMPRODUCT then multiplies the columns of logicals (turned into 1 and 0) row by row and then sums it up. Thus you get a count of rows where the above conditions are all TRUE.
Best
Darek
By the way, I got really busy at work and then forgot to formally thank you for helping me with this. It was so incredibly helpful to help me understand and convert the formula to DAX. So, thank you. 🙂
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |