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        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
Need Help
  • amitchandak's avatar
    amitchandak
    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()))

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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's avatar
      Elisa112
      Icon for Helper V rankHelper V
      2 years ago

      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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Elisa112
      Icon for Helper V rankHelper V
      2 years ago

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

      Thanks again!