Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

sample on date with Norm validy between two dates

Hi,   I have a two tables 1. "Norm" that have an itemname, value max, value min and startdate and enddate. 2. Samples that have a sampledate, normitem and measured value   I want to know how ma...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    • Count of All matched values in 2021-Jan
    Count (2021-Jan) = 
var _t=SUMMARIZE(FILTER(ALL('Sample'),[Norm]=MAX('Norm'[Normitem]) && [Value] >=MIN('Norm'[Min]) && [Value]<=MAX('Norm'[Max]) && [SampleDate] >=MAX('Norm'[StartDate]) && [SampleDate]<=MAX('Norm'[EndDate]) &&YEAR([SampleDate])=2021 && MONTH([SampleDate])=1),[SampleID])
return COUNTX(_t,[SampleID])+0
    • Count of Valid:
    Valid of 2021 Jan = SUMX(FILTER('Norm', [Count (2021-Jan)]>0),[Count (2021-Jan)])
    • Count of Not Valid:
    Not Valid of 2021 Jan = COUNTROWS(FILTER('Sample',YEAR([SampleDate])=2021 && MONTH([SampleDate])=1)) -[Valid of 2021 Jan]

     

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

  • Count of All matched values in 2021-Jan
Count (2021-Jan) = 
var _t=SUMMARIZE(FILTER(ALL('Sample'),[Norm]=MAX('Norm'[Normitem]) && [Value] >=MIN('Norm'[Min]) && [Value]<=MAX('Norm'[Max]) && [SampleDate] >=MAX('Norm'[StartDate]) && [SampleDate]<=MAX('Norm'[EndDate]) &&YEAR([SampleDate])=2021 && MONTH([SampleDate])=1),[SampleID])
return COUNTX(_t,[SampleID])+0
  • Count of Valid:
Valid of 2021 Jan = SUMX(FILTER('Norm', [Count (2021-Jan)]>0),[Count (2021-Jan)])
  • Count of Not Valid:
Not Valid of 2021 Jan = COUNTROWS(FILTER('Sample',YEAR([SampleDate])=2021 && MONTH([SampleDate])=1)) -[Valid of 2021 Jan]

 

Output:

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.