Combined IF-statements from two different tables

[MarcLykke]

Hi everyone

I am trying to create a calculated column with an IF-statement from two different tables.

In table 1 I have all the cases.

In table 2 I have all the meetings.

Each case can have several meetings and the two tables are linked together by caseid.

Table 1:

Caseid	Case start date	Case deadline date
1	Date 1	Date 1
2	Date 2	Date 2

Table 2:

Case id	Meeting type	Meeting date
1	Phone	Date 1
1	Personal	Date 2

I would like to create a calculated column in table 1 which shows the number of meetings for the specific case between the case start date and the case deadline date.

There are several other conditions in the two tables that I want to combine, but I would like to start with this simple one in order to understand the logic behind the correct DAX.

I have tried with a simple IF-statement followed by COUNTROWS, but it doesn't seem to work with conditions from two different tables.

Can anyone help me?

[amitchandak · Accepted solution]

@MarcLykke , a new column in table 1

countx(filter(Table2, Table1[CaseID] = Table2[CaseID] && Table2[Meeting date] >= Table1[Case Start Date] && Table2[Meeting date] <= Tabel1[Case Deadline Date] ) Table2[Meeting Type] )

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8