Forum Discussion
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.⚡
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.⚡
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?
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.
Kelly