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 many samples there are in a selected period that have a value that is between the min and max value of the norm that is validity in the date of the sample

 

Norm

NormitemMinMaxStartDateEndDate
A5801-01-202101-02-2021
A51501-02-202101-05-2021
B202501-1-2021 

 

Sample

SampleIDNormValueSampleDate
1A701-01-2021
2A1315-01-2021
3B1801-03-2021

 

Sample 1 is valid

Sample 2 is NOT valid

Sample 3 is NOT valid

 

Now I want a measure that counts how many samples in January 2021 are valid and NOT valid

 

Note: I want one measure and without using a calculated column

 

Can someone give me a solution or link to an information page?

 

Thanks

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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try measure like

     

    Measure =
    var _max = maxx(allselected('Date'), 'Date'[Date])
    return
    maxx(Table, if( Table[Start Date] <= _max && Table[End Date] >= _max, "Valid", "Not Valid"))

  • 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.