Forum Discussion
Identifying resamples
I have a large table of laboratory samples. I am interested in building a column that shows whether a given sample point was resampled on that day.
Conceptually, this is simple--if the same sample point is entered more than once on the same day, return True. I can easily imagine how I would accomplish this with procedural code, but expressing it in DAX is giving me quite a bit of trouble.
I've included a table below showing the kind of data I'm working with. Again, the "Resampled" column on the right is what I am trying to construct, but it does not currently exist.
Keep in mind that each resample is an entirely new entry with a unique ID.
Also, I am not interested in filtering a visual by individual dates and counting the duplicates--I need to have each row in the table flagged with a "Resampled" value automatically. Any thoughts would be greatly appreciated.
| ID | Sampled Date | Sampling Point | Resampled |
| 9001 | 1/1/2020 | A | True |
| 9002 | 1/1/2020 | B | False |
| 9003 | 1/1/2020 | C | False |
| 9004 | 1/1/2020 | D | False |
| 9005 | 1/1/2020 | A | True |
| 9006 | 1/2/2020 | C | True |
| 9007 | 1/2/2020 | C | True |
| 9008 | 1/3/2020 | D | False |
| 9009 | 1/3/2020 | B | False |
HI,
Can you try below formula 🙂
Resampled =
IF (
CALCULATE (
COUNTA ( check[Sampling Point] ),
ALLEXCEPT ( check, check[Sampled Date], check[Sampling Point ] )
) > 1,
TRUE (),
FALSE ()
)Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂Regards,
N V Durga Prasad
4 Replies
HI,
Can you try below formula 🙂
Resampled =
IF (
CALCULATE (
COUNTA ( check[Sampling Point] ),
ALLEXCEPT ( check, check[Sampled Date], check[Sampling Point ] )
) > 1,
TRUE (),
FALSE ()
)Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂Regards,
N V Durga Prasadjnw022 -
Resampled Column = VAR __Date = [Sampled Date] VAR __Count = COUNTROWS(FILTER(ALL('Table'),[Sampled Date]=__Date)) RETURN IF(__Count = 1,FALSE(),TRUE()) Resampled Measure = VAR __Date = MAX([Sampled Date]) VAR __Count = COUNTROWS(FILTER(ALL('Table'),[Sampled Date]=__Date)) RETURN IF(__Count = 1,FALSE(),TRUE())Also, since I see you are a New Member.
Please check out this article: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Greg_Deckler , thank you for your prompt response.
However, from what I can tell, your solution will tell me whether more than one sample at all was taken on the same day. What I am interested in is whether more than one sample from the same sample point was taken on the same day. That is, the [Resampled] column should only evaluate to true if >1 samples from A (or B, C, D) were taken on the same day. There will certainly be multiple samples from many different places every day, but I'm only trying to flag when we catch the same one multiple times.
If the background helps, I'm an industrial chemist who is tracking sample burden for a plant. Ideally there would be only one sample for each required location per day, but sometimes the results are suspect and we need to catch more of them. There is value in being able to distinguish when a sample was caught for that reason, rather than as part of the regular schedule.
jnw022 - Oh yeah, forgot to include Sampling Point:
Resampled Column = VAR __Date = [Sampled Date] VAR __Point = [Sampling Point] VAR __Count = COUNTROWS(FILTER(ALL('Table'),[Sampled Date]=__Date) && [Sampling Point] = __Point) RETURN IF(__Count = 1,FALSE(),TRUE()) Resampled Measure = VAR __Date = MAX([Sampled Date]) VAR __Point = MAX([Sampling Point]) VAR __Count = COUNTROWS(FILTER(ALL('Table'),[Sampled Date]=__Date) && [Sampling Point] = __Point) RETURN IF(__Count = 1,FALSE(),TRUE())
