Creating relationship and summarizing 1 to many table

[Anonymous]

I have Study Table:

StudyID	IRB	ProtoType	Type
1	22234	out	other
2	5556	in	new
3	33899	in	old
4	99567	in	new
5	1123	out	new
6	9432	out	old
7	28766	n/a	new

Patient Table

Studyid	PatID	statusdate	Location
1	sd34546	1/1/2009	in
1	sd90992	5/23/2009	us
2	sd147438	11/15/2010	russia
4	sd203884	1/1/2009	japan
3	sd260330	5/23/2011	in
3	sd316776	11/15/2012	us
3	sd373222	1/1/2017	russia
6	sd429668	1/1/2009	japan
6	sd486114	5/23/2018	in
7	sd542560	11/15/2019	us
2	sd599006	1/1/2009	russia
4	sd655452	5/23/2013	japan

I want to summarize to create a table below

StudyID	IRB	ProtoType	Type	PatCount
1	22234	out	other	2
2	5556	in	new	2
3	33899	in	old	3
4	99567	in	new	2
5	1123	out	new	0
6	9432	out	old	2
7	28766	n/a	new	1

I want to create a slice on the patient table[statusdate] and want to filter based on the statusdate

I have a problem creating relationships between table and the date.

How do i create the PatCount measure

[amitchandak]

@Anonymous , if you want to create a new table .

You can merge in power query it will give the option of aggregation when you expand

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

In dax refer

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

If the are one to many relation you can create measure like this

measure = count(Patient[PatID])

measure = count(Patient[PatID])+0 //to display all record

[ryan_mayu]

@Anonymous 

you can create a new column

Column = CALCULATE(COUNTROWS(patient),FILTER(patient,patient[Studyid]=study[studyID]))+0

[Anonymous]

I also want to use the slicer in the visualization and abe able to filter by statusdate in patient table.

If we don't have a relationship how can we filter it?

[ryan_mayu]

@roopesh

if you do not have the relationship between two tables, use the TREATAS function

Measure = CALCULATE(COUNT(patient[PatID]),TREATAS(VALUES(study[StudyID]),patient[Studyid]))+0