Summarize data from two tables filtering on category

[Elisa112]

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        Status      ConfirmationDate     ApprovalDate   

001            LEH         Active         10 June                   11 June

002            MJJ         Inactive      9 May                      12 May

003            BNN       Active       8 April                      16 April

Meetings

Cust ID   MeetID  MeetDate  MeetingType  

001         005        13 June      Introduction

002         007         14 May     Assess

003         009           18 April   Introduction

I need to create a summarise table to include active customers only.  The summarised table should include the confirmation date and approval date from the Customer table but only Introduction meetings from the Meetings table. I also need to show the date difference in days between  confirmation and approval dates.

my cnrrent dax works ok but I am having trouble filtering to active customers only, here is my current logic:

SUMMARIZECOLUMNS(

    'Meetings'[CustomerID],'Meetings'[Status],'Staff'[Name], 'Customer'[Confirmation_date],

    "Introductory Meeting",CALCULATE(

        MIN('Meetings'[Meeting Date]),

        FILTER(

            'Meetings',

            'Meetings'[CustomerID] = SELECTEDVALUE('Meetings'[CustomerID]) &&'Meetings'[Type] = "Introduction") )

            )

I have created a separate measure to calculate the date differences on the above summary table which also works fine, so the only issue is filtering the active users from the customer table.

Any suggestions greatly received

thanks in advance

[Anonymous]

Hi @Elisa112 ,

I create two tables as you mentioned.

Then I create a new table and here is the DAX code.

Table = 
SUMMARIZECOLUMNS (
    'Customer'[CustID],
    'Customer'[Staff],
    'Customer'[ConfirmationDate],
    'Customer'[ApprovalDate],
    "Introductory Meeting",
        CALCULATE (
            MIN ( 'Meetings'[MeetDate] ),
            FILTER (
                'Meetings',
                'Meetings'[Cust ID] = SELECTEDVALUE ( 'Customer'[CustID] )
                    && 'Meetings'[MeetingType ] = "Introduction"
            )
        )
)

Finally I create a measure and get what you want.

Measure = DATEDIFF(MAX('Customer'[ConfirmationDate]),MAX('Customer'[ApprovalDate]),DAY)

Best Regards

Yilong Zhou

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

[Elisa112]

@Anonymous I didnt use your solution but thank you for your help and time. I may use it another time.

Thanks again!

[amitchandak · Accepted solution]

@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]

@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!