topic Re: Could someone help convert this Excel formula to DAX? in DAX Commands and Tips

Could someone help convert this Excel formula to DAX?

isohawon — Fri, 02 Aug 2019 14:53:23 GMT

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 :)

Re: Could someone help convert this Excel formula to DAX?

Anonymous — Fri, 02 Aug 2019 17:09:50 GMT

-- 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:

take the year from C23 -> Y = year(C23)
mark the cells in TrackingSheet!$E$5:$E$133 as TRUE when the year of the value is = Y
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)
mark the cells in TrackingSheet!$H$5:$H$133 as TRUE when they have "Yes" in them
mark the cells in TrackingSheet!$G$5:$G$133 as TRUE when they don't have "relapsing" in them
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

Re: Could someone help convert this Excel formula to DAX?

isohawon — Thu, 22 Aug 2019 14:28:49 GMT

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. :)

Re: Could someone help convert this Excel formula to DAX?

Anonymous — Thu, 22 Aug 2019 16:10:29 GMT

No worries. Thanks :)

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

Best
Darek