cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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

 Normitem Min Max StartDate EndDate A 5 8 01-01-2021 01-02-2021 A 5 15 01-02-2021 01-05-2021 B 20 25 01-1-2021

Sample

 SampleID Norm Value SampleDate 1 A 7 01-01-2021 2 A 13 15-01-2021 3 B 18 01-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
Community Support

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 2
Community Support

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.

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors