cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Excluding based on criteria

Hi, hope this makes sense. But I have a list of claims that can have multiple release dates such as below:

PowerBI123456_5-1594418272314.png

 

Each claim might have an edit associated with it below:

 

PowerBI123456_3-1594418235676.png

 

I want to exclude edits where there are multiple claim releases and if any of those release dates are before 1/1/2020. So the results would be:

PowerBI123456_6-1594418316727.png

 

I am dealing with million + rows. If anyway to do this in power query, that would be preferrable. Thanks!

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User VII
Super User VII

Re: Excluding based on criteria

@PowerBI123456 add a new column in power query using following M code, assuming Jan 01st, 2020 is a fixed date

 

if [Edit Date] < #date(2020,1,1) then "Yes" else "No"

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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

Highlighted
Super User VII
Super User VII

Re: Excluding based on criteria

@PowerBI123456 See attached solution, tweak it as per your need. I hope this will get you going.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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

7 REPLIES 7
Highlighted
Super User VII
Super User VII

Re: Excluding based on criteria

@PowerBI123456 add a new column in power query using following M code, assuming Jan 01st, 2020 is a fixed date

 

if [Edit Date] < #date(2020,1,1) then "Yes" else "No"

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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

Highlighted
Helper IV
Helper IV

Re: Excluding based on criteria

@parry2k  Thanks, but its not dependent on the edit date. I want to exclude edits where there are multiple claim releases for one particular claim ID and if any of those release dates are before 1/1/2020. Hope that makes sense!

Highlighted
Super User VII
Super User VII

Re: Excluding based on criteria

@PowerBI123456 not entirely.






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.





Highlighted
Helper IV
Helper IV

Re: Excluding based on criteria

@parry2k So looking at the example below:

 

Claim A: I want to keep because there is only one claim A. 

Claim B: I want to exclude because where is more than one claim B, and one of the release dates is before 1/1/2020. 

Claim C: I want to keep because even though there is more than one claim C, both release dates are after 1/1/2020. 

 

The edit date doesn't matter. Does that help?

 

PowerBI123456_0-1594497859381.png

 

Highlighted
Super User VII
Super User VII

Re: Excluding based on criteria

@PowerBI123456 See attached solution, tweak it as per your need. I hope this will get you going.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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

Highlighted
Community Support
Community Support

Re: Excluding based on criteria

Hi  @PowerBI123456 ,

 

Create a calculated column as below:

 

Check = 
VAR _count=CALCULATE(COUNT('Table'[Claim]),FILTER('Table','Table'[Claim]=EARLIER('Table'[Claim])))
var _mindate=CALCULATE(MIN('Table'[Release]),FILTER('Table','Table'[Claim]=EARLIER('Table'[Claim])))
Return
IF(_count>1&&_mindate<DATE(2020,1,1),"Yes","No")

 

Then create a measure as below:

 

Measure = LOOKUPVALUE('Table'[Check],'Table'[Claim],MAX('Table (2)'[Claim]),blank())

 

Then you will see:

Annotation 2020-07-13 132421.png

Pls check the attachment for details.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Highlighted
Helper IV
Helper IV

Re: Excluding based on criteria

@parry2k  thank you!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors