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!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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
Top Kudoed Authors