cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amandaw
Frequent Visitor

Filter out all rows with at least one column >=3

Hi everyone,

 

I am creating a DAX measure for a matrix visual. I have no idea how to write the DAX or any other way. The requirement is that the matrix has only the rows with at least one column has data larger than 3. For example, if a row is (0, 0, 0, 1, 2, 3, 0), then this row should be kept. If a row is (1,1,1,2,1,2,1), then this row should be filtered out.

 

The DAX I use is this:

Capture0.PNG

 

Blue arrows are showing which rows I want to have. The matrix without filtering is this:

Capture.PNG

 

Thank you in advance for your help! 

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@amandaw I don't think you followed the instructions, you don't need to use this measure in the matrix visual but use it in visual level filter, keep your matrix the way it is.

 

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

6 REPLIES 6
parry2k
Super User III
Super User III

@amandaw glad it worked, enjoy!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





amandaw
Frequent Visitor

Hi @parry2k, could you please explain how does this DAX measure work? I don't really understand why does SUMX work in this case. Thank you in advance!

parry2k
Super User III
Super User III

@amandaw I don't think you followed the instructions, you don't need to use this measure in the matrix visual but use it in visual level filter, keep your matrix the way it is.

 

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

amandaw
Frequent Visitor

OMG, parry2k, you are a genius!! Thank you so much!

parry2k
Super User III
Super User III

@amandaw I think you want larger or equal to 3, add the following measure and then use it in the visual level filter where the value is greater than 0

 

Filter = SUMX ( VALUES ( 'Table'[Week Name Column] ), IF ( [Measure] >= 3, 1, 0 ) )

 

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





amandaw
Frequent Visitor

Hi parry2k, 
 
Thank you for your replying.
 
I tried your way. It makes all numbers larger than 3 become 1, but other columns in the same row become 0. I want to keep the data in other columns if this row has at least one data >=3. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors