Forum Discussion

Elisa112's avatar
Elisa112
Icon for Helper V rankHelper V
2 years ago
Solved

Summarize data from two tables filtering on category

Hi Experts   I am having roubling getting the correct output in a summarised table, I have 2 tables joined on a customerid which have the following data     Customer CustID       Staff        S...
  • amitchandak's avatar
    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()))