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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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