cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to filter out rows from calculation

Hi all,

 

I was wondering if anybody could help me with what I think should be achieved very simply in DAX - I just can't find the right function!

 

I have two columns of data, let's say Column A and Column B. I'd like to SUM Column A based on the values in Column B.

With the example data below, I'd like to sum Column A for all values in Column B EXCLUDING blanks and 'Don't know'. So my total sum should be only for 'yes' and 'no' and therefore be 65. So my question is: how do I exclude specific values in a column from a calculation?

Harriet_1-1624962083285.png

Any help much appreciated!

Thanks 😊

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Picture1.png

 

Result measure =
SUMX ( FILTER ( 'Table', NOT ( 'Table'[B] IN { "D", "" } ) ), 'Table'[A] )

 

 

https://www.dropbox.com/s/ou6ab6bsvhri2pe/harriet.pbix?dl=0 

 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Picture1.png

 

Result measure =
SUMX ( FILTER ( 'Table', NOT ( 'Table'[B] IN { "D", "" } ) ), 'Table'[A] )

 

 

https://www.dropbox.com/s/ou6ab6bsvhri2pe/harriet.pbix?dl=0 

 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

CNENFRNL
Community Champion
Community Champion

Excel, our oldie but goodie does the trick with ease,

Untitled.png

Anonymous
Not applicable

Thanks for this, but I need to do this within Power BI using a measure

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors