Forum Discussion
Summarize data from two tables filtering on category
- 2 years ago
Elisa112 , Try like , Join two tables on customer ID.
SUMMARIZECOLUMNS(
'Customer'[CustID],
'Customer'[Staff],
'Customer'[ConfirmationDate],
'Customer'[ApprovalDate],
"Days Between", 'Customer'[ApprovalDate] - 'Customer'[ConfirmationDate],
"Introductory Meeting",
CALCULATE(
MIN('Meetings'[MeetDate]),
'Meetings'[MeetingType] = "Introduction"
),
FILTER(
'Customer',
'Customer'[Status] = "Active"
)
)or a meausre like
Active Customers with Intro Meeting =
CALCULATE(
COUNTROWS('Customer'),
'Customer'[Status] = "Active",
CALCULATE(
COUNTROWS('Meetings'),
'Meetings'[MeetingType] = "Introduction",
RELATEDTABLE('Meetings')
) > 0
)Measures like
M1 = CALCULATE( COUNTROWS('Customer'), filter('Customer' 'Customer'[Status] = "Active"))
M2 = CALCULATE(COUNTROWS('Meetings'), Filter('Meetings', 'Meetings'[MeetingType] = "Introduction" ))
Active Customers with Intro Meeting = sumx(Values('Customer'[CustID]), if(Not(isblank(M1)) && Not(isblank(M2)), [CustID], blank()))
Elisa112 , Try like , Join two tables on customer ID.
SUMMARIZECOLUMNS(
'Customer'[CustID],
'Customer'[Staff],
'Customer'[ConfirmationDate],
'Customer'[ApprovalDate],
"Days Between", 'Customer'[ApprovalDate] - 'Customer'[ConfirmationDate],
"Introductory Meeting",
CALCULATE(
MIN('Meetings'[MeetDate]),
'Meetings'[MeetingType] = "Introduction"
),
FILTER(
'Customer',
'Customer'[Status] = "Active"
)
)
or a meausre like
Active Customers with Intro Meeting =
CALCULATE(
COUNTROWS('Customer'),
'Customer'[Status] = "Active",
CALCULATE(
COUNTROWS('Meetings'),
'Meetings'[MeetingType] = "Introduction",
RELATEDTABLE('Meetings')
) > 0
)
Measures like
M1 = CALCULATE( COUNTROWS('Customer'), filter('Customer' 'Customer'[Status] = "Active"))
M2 = CALCULATE(COUNTROWS('Meetings'), Filter('Meetings', 'Meetings'[MeetingType] = "Introduction" ))
Active Customers with Intro Meeting = sumx(Values('Customer'[CustID]), if(Not(isblank(M1)) && Not(isblank(M2)), [CustID], blank()))
- Elisa1122 years ago
Helper V
amitchandak Thank you this works, I had to edit the join a little and delete the days between column as I could not get it to work but overall this has helped my understanding a lot.
You are a star!