Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Norbertus
Helper V
Helper V

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

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @Norbertus ,

 

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

Eyelyn9_0-1639383834532.png

 

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.

View solution in original post

2 REPLIES 2
v-eqin-msft
Community Support
Community Support

Hi @Norbertus ,

 

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

Eyelyn9_0-1639383834532.png

 

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.

amitchandak
Super User
Super User

@Norbertus , 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"))

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.