locked
Intersection of two subsets? RRS feed

  • Question

  • Hi all,

    I have a set of data in PowerPivot with one row per form, where a form represents a stage in a process.  See below for an example. 

    Each form has a client ID and a start date among other things.  The forms have names, such as  "Contact assessment form", "Reablement form" etc.  

    I want to find, using DAX, the number of unique clients who made a transition between two stages in the process in a given timeframe.  So, to find (for example) the number of clients who went from Contact stage to Reablement stage, I would  generate a table of all those who had a Contact assessment form, and a table of all those who had a Reablement form, and find the client IDs which appear in both.  I could do this manually in excel easily, but want to crack it using DAX.  I'm sure it's a simple answer but I'm a DAX newbie and really struggling here.  Any help appreciated!

    Client ID Assessment Type Start Date
    249837 Contact   assessment form 01/01/2014
    249837 Reablement form 04/01/2014
    249837 Assessment form 01/01/2014
    249837 Support Planning In Progress Form 20/01/2014
    249837 Support Plan 31/01/2014
    249837 Support Plan in Place Form 02/02/2014
    249837 Review Form 01/01/2015
    249837 Support Plan 05/01/2015
    4391283 Contact   assessment form 01/06/2014
    4391283 Assessment form 10/06/2014
    4391283 Support   Planning In Progress Form 30/07/2014
    4391283 Support Plan 30/09/2014
    4391283 Support Plan in   Place Form 30/09/2014
    329834 Contact assessment form 15/06/2014
    398341 Contact   assessment form 30/06/2005
    398341 Assessment form 05/07/2005
    398341 Support   Planning In Progress Form 30/07/2005
    398341 Support Plan 25/08/2005

    Wednesday, March 25, 2015 2:42 PM

Answers

  • Hi Giles,

    sorry, was completely on the wrong path here…

    But your approach with the 2 tables looks fine: Just connect them with one DimDate-table via StartDate.

    Also connect these tables with one shared “DimClient” table (master data: just one row per client).

    Then add another table that contains of 3 columns with your conditions (DimCond):

    CondNo

    Cond1

    Cond2

    1

    Contact   assessment form

    Reablement form

    2

    Reablement form

    Assessment form

    Connect Cond1 to your first table (Condition) and Cond2 to your second.

    Write a simple COUNT([ClientID])-measure in each of your fact tables (Count1 and Count2).

    Then comes the trick: Write this measure in your DimDate-table:

    Count:= IF([Count1]=1 &&[Count2]=1;1;blank())

    This way the necessary match of Cond1 and Cond2 will automatically be related to the specific time granularity that you choose (month, week, year..)

    Make sure that you take all column and row fields from your Dim-tables and not from your Fact-tables (Assessment data). I.e. month from your DimDate in columns and CondNo from DimCond in rows.


    Imke



    Friday, March 27, 2015 6:38 PM
    Answerer
  • Hi Giles,

    sorry - these details...

    Take this measure instead:

    Count:= IF([Count1]>=1 &&[Count2]>=1;[Count1];blank())

    Warning about missing relationships can be ignored, this is not actually an error.


    Imke

    • Marked as answer by Charlie Liao Friday, April 3, 2015 1:38 AM
    Tuesday, March 31, 2015 2:13 PM
    Answerer

All replies

  • Hi Giles,

    so you basically count twice and apply a filter, like:

    CALCULATE(DSICINCTCOUNT(ClientID), FILTER(YourTable, DISTINCTCOUNT(AssesmentType > 1))


    Imke

    Thursday, March 26, 2015 6:52 AM
    Answerer
  • Thanks Imke - that gives me anyone who moves from any stage to any other, i.e. a count of all clients who have more than one type of form against, but I need to be able to count specific transitions.  So I want to set up separate measures to count:

    a) All clients with a Contact Assessment form AND a Reablement form within a time period

    b) All clients with a Reablement form AND an Assessment form within a time period

    etc. Can anyone help?

    Thursday, March 26, 2015 9:05 AM
  • Hi Giles,

    you can write that in your FILTER-Statement like:

    FILTER(AssessmentType="Contact Assessment form" &&  AssessmenttType="Reablement form")


    Imke

    Thursday, March 26, 2015 10:10 AM
    Answerer
  • This doesn't return a value, because no rows in the table contain both Assessment Types.  I want to count the client IDs of anyone who has an incidence of both types of Assessment Type within the same table.  I've tried importing a second copy of the Assessments data, but PowerPivot won't let me apply a filter against that second copy of the table.  Can anybody help?

    Friday, March 27, 2015 1:53 PM
  • Hi Giles,

    sorry, was completely on the wrong path here…

    But your approach with the 2 tables looks fine: Just connect them with one DimDate-table via StartDate.

    Also connect these tables with one shared “DimClient” table (master data: just one row per client).

    Then add another table that contains of 3 columns with your conditions (DimCond):

    CondNo

    Cond1

    Cond2

    1

    Contact   assessment form

    Reablement form

    2

    Reablement form

    Assessment form

    Connect Cond1 to your first table (Condition) and Cond2 to your second.

    Write a simple COUNT([ClientID])-measure in each of your fact tables (Count1 and Count2).

    Then comes the trick: Write this measure in your DimDate-table:

    Count:= IF([Count1]=1 &&[Count2]=1;1;blank())

    This way the necessary match of Cond1 and Cond2 will automatically be related to the specific time granularity that you choose (month, week, year..)

    Make sure that you take all column and row fields from your Dim-tables and not from your Fact-tables (Assessment data). I.e. month from your DimDate in columns and CondNo from DimCond in rows.


    Imke



    Friday, March 27, 2015 6:38 PM
    Answerer
  • Amazing!  Thank you Imke.
    Monday, March 30, 2015 3:14 PM
  • Hi Imke,

    This all makes sense, unfortunately I can't get this to work - it returns the 'Relationship may be needed' error, and no cell in the pivot returns more than 1. 

    Actually the pivot table returns this error pretty whatever measures and columns I include, so I think there's a problem with the data model.  I've not included any row or column headers from the fact tables, only dim tables.

    I have relationships set up from each of the FactAssessment tables to a) the client table (1 row per client), b) the date table (1 row per date) and the two columns in the Condition table as you suggested above, but it's still saying a relationship is missing. 

    Should there be a direct relationship between the two FactAssessment tables?  Could there be another relationship missing?

    Giles

    Tuesday, March 31, 2015 10:03 AM
  • Hi Giles,

    sorry - these details...

    Take this measure instead:

    Count:= IF([Count1]>=1 &&[Count2]>=1;[Count1];blank())

    Warning about missing relationships can be ignored, this is not actually an error.


    Imke

    • Marked as answer by Charlie Liao Friday, April 3, 2015 1:38 AM
    Tuesday, March 31, 2015 2:13 PM
    Answerer