Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
isohawon
Frequent Visitor

Could someone help convert this Excel formula to DAX?

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 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

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
Not applicable

No worries. Thanks 🙂

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

Best
Darek

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors