Counting all related values based on related tables

[SO]

Hello All, 

I have what I think is a common challenge but is often described differently.  Most of the challenges I see are about filtering all of the results based on 1 or more filters.  In this case, I am looking to filter on one values and expand the counts of the related values in a second column. 

I am looking to summarize my data like this

Person	ClassCode	Course	Mark	Sum	Average	Model
1	A	Math	50	140	70	Online
1	G	Music	90	140	70	Online
3	C	Science	70	140	70	F2F
3	E	PhysEd	70	140	70	F2F

As my data is In Service, I have a few many different tables to contend with, but imagine they looked like this

Class - Table:

ClassCode	ClassID	Start Date	End Date
A	M1	9/9/2021	2/14/2022
B	E1	9/9/2021	2/14/2022
C	S1	9/9/2021	2/14/2022
D	G1	9/9/2021	2/14/2022
D	H1	2/14/2022	6/30/2022
E	S1	9/9/2021	2/14/2022
E	P1	9/9/2021	2/14/2022
F	A1	9/9/2021	2/14/2022
G	M1	9/9/2021	6/30/2022
C	L1	9/9/2021	2/14/2022
A	C1	2/14/2022	6/30/2022
F	T1	9/9/2021	2/14/2022

Person	ClassID	Start Date	End Date
1	M1	9/9/2021	2/14/2022
2	E1	9/9/2021	2/14/2022
3	S1	9/9/2021	2/14/2022
4	G1	9/9/2021	2/14/2022
1	H1	2/14/2022	6/30/2022
2	S1	9/9/2021	2/14/2022
3	P1	9/9/2021	2/14/2022
4	A1	9/9/2021	2/14/2022
1	M1	9/9/2021	6/30/2022
2	L1	9/9/2021	2/14/2022
3	C1	2/14/2022	6/30/2022
4	T1	9/9/2021	2/14/2022

Course  - Table

Person	Course	Mark	Model
1	Math	50	Online
2	English	60	F2F
3	Science	70	F2F
4	Geography	80	F2F
1	History	90	Online
2	Social	100	F2F
3	PhysEd	70	F2F
4	Art	80	F2F
1	Music	90	Online
2	Leadership	60	F2F
3	Coop	60	Online
4	Tech	80	F2F

In theory it would be great to show all the data together, but there actually isn't a "unique" code or acommon field linking all the tables together.

Person	ClassCode	Course	Mark	Model	Start Date	End Date
1	A	Math	50	Online	9/9/2021	2/14/2022
2	B	English	60	F2F	9/9/2021	2/14/2022
3	C	Science	70	F2F	9/9/2021	2/14/2022
4	D	Geography	80	F2F	9/9/2021	2/14/2022
1	D	History	90	Online	2/14/2022	6/30/2022
2	E	Social	100	F2F	9/9/2021	2/14/2022
3	E	PhysEd	70	F2F	9/9/2021	2/14/2022
4	F	Art	80	F2F	9/9/2021	2/14/2022
1	G	Music	90	Online	9/9/2021	6/30/2022
2	C	Leadership	60	F2F	9/9/2021	2/14/2022
3	A	Coop	60	Online	2/14/2022	6/30/2022
4	F	Tech	80	F2F	9/9/2021	2/14/2022

So the question is, if Code = A and StartDate < 2/13/2022 then Sum (or average, or count, etc.) all Marks for the person.  Example: 

Person	ClassCode	Course	Mark	Sum	Average	Model
1	A	Math	50	140	70	Online
1	G	Music	90	140	70	Online
3	C	Science	70	140	70	F2F
3	E	PhysEd	70	140	70	F2F

I know my DAX below this is incorrect, as I've been trying to find an "In" or "All others" type of function. 

Any help is appreciated...

Measure = 

var F1_2021 = DATEVALUE("02/13/2022")

var enrol = FILTER(ClassCode, (ClassCode [StartDate] <= F1_2021)

Var _CC = FILTER(RELATEDTABLE(ClassCode), Class[ClassCode] = "A"))

Return

CALCULATE(COUNT(Course[Person] ), enrol, _CC)

Many Thanks 

And Thanks to few folks that I've used to consider this challenge 

@Baskar, @AlexisOlson; and @amitchandak

[SO]

Many thanks Liu Yang, but what I have is "In Service" through Analysis Services.  This would work amazingly if this were in Desktop, but then we'd have a significant speed issue.  Hmmm.. Thanks for the great attempt though!  Much appreciated.  

[v-yangliu-msft]

Hi  @SO ,

Here are the steps you can follow：

1. In Power query, add Index to 3 tables, Add Column – Index Column – From 1.

2. Create calculated column.

Person =
 CALCULATE(MIN('Person'[Person]),FILTER(ALL('Person'),'Person'[ClassID]=EARLIER(Class[ClassID])&&'Person'[Index]=EARLIER('Class'[Index])))

Course =
CALCULATE(MAX('Course'[Course]),FILTER(ALL('Course'),'Course'[Person]=EARLIER('Class'[Person])&&'Course'[Index]=EARLIER('Class'[Index])))

Mark = CALCULATE(MAX('Course'[Mark]),FILTER(ALL('Course'),'Course'[Person]=EARLIER('Class'[Person])&&'Course'[Index]=EARLIER('Class'[Index])))

Model =
CALCULATE(MAX('Course'[Model]),FILTER(ALL('Course'),'Course'[Person]=EARLIER('Class'[Person])&&'Course'[Index]=EARLIER('Class'[Index])))

3. Create calculated Table.

Summ_Table = SUMMARIZE('Class','Class'[Person],'Class'[ClassCode],'Class'[Course],'Class'[Mark],'Class'[Model],'Class'[Start Date],'Class'[End Date])

If Code = A and StartDate < 2/13/2022, there is only one piece of data, which does not meet your expected results. Can you explain in detail.

You can also use the following dax to achieve this:

Create calculated Table

Question_table =
var _1=FILTER('Summ_Table','Summ_Table'[Course] in {"Math","Music","Science","PhysED"})
return
SUMMARIZE(_1,[Person],[ClassCode],[Course],[Mark],[Model],
"Sum",SUMX(FILTER(_1,[Person]=EARLIER([Person])),[Mark]),
"Avg",AVERAGEX(FILTER(_1,[Person]=EARLIER([Person])),[Mark]))

Best Regards,

Liu Yang

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