Excluding based on criteria

[PowerBI123456]

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

Each claim might have an edit associated with it below:

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:

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

[parry2k · Accepted solution]

@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.⚡

[parry2k · Accepted solution]

@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.⚡

[PowerBI123456]

@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!

[parry2k]

@PowerBI123456 not entirely.

[PowerBI123456]

@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?

[v-kelly-msft]

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:

Pls check the attachment for details.

Best Regards,
Kelly

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

[parry2k · Accepted solution]

@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.⚡

[PowerBI123456]

@parry2k  thank you!