Could someone help convert this Excel formula to DAX?

[isohawon]

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 🙂

[Anonymous · Accepted 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:

1. take the year from C23 -> Y = year(C23)
2. mark the cells in TrackingSheet!$E$5:$E$133 as TRUE when the year of the value is = Y
3. mark the cells in TrackingSheet!$E$5:$E$133 as TRUE when they are not equal to "" (this condition is redundant in view of the one above)
4. mark the cells in TrackingSheet!$H$5:$H$133 as TRUE when they have "Yes" in them
5. mark the cells in TrackingSheet!$G$5:$G$133 as TRUE when they don't have "relapsing" in them
6. mark the cells in TrackingSheet!$E$5:$E$133 as TRUE if the month = month( C23 )

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

[isohawon]

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. 🙂

[Anonymous]

No worries. Thanks 🙂

When there's a will, there's always a way. As they say.

Best
Darek