Forum Discussion

cobus_19's avatar
cobus_19
Frequent Visitor
9 years ago
Solved

creating a summary table

Hi


So I have a date set with a structure like this:

origin date | client | client id | xxx | xxx | status 1 date | status 2 date | etc

 

Each row is a unique client and contains a unique client id

My problem is essentially this, I want to filter the data monthly to see how many clients went through each status.

My problem is that the status's are sometimes seperated by as much as a week.

Thus filtering "origin date" to October, for example, I won't see if any of the clients with "origin date" prior to october enter any new statuses in October.

I was thinking I could create a summary table of the structure:
date | status 1 count | status 2 count | status 3 count | etc

But i'm having a hard time creating this using PowerQuery. If this was excel I would just use COUNTIF("date"="status x date"), but I can't find a similar functionality for PowerQuery, I would very much appreciate some help.

Regards
Cobus

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi cobus_19,

     

    You can use summarize function(DAX) to achieve your requirement.

     

    Table = SUMMARIZE(Sheet1,Sheet1[origin date],"State1",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 1 date]),"State2",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 2 date]),"State3",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 3 date]))

     

    If above is not help, please provide some sample data.

     

    Regards,

    Xiaoxin Sheng

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi cobus_19,

     

    You can use summarize function(DAX) to achieve your requirement.

     

    Table = SUMMARIZE(Sheet1,Sheet1[origin date],"State1",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 1 date]),"State2",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 2 date]),"State3",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 3 date]))

     

    If above is not help, please provide some sample data.

     

    Regards,

    Xiaoxin Sheng

    • cobus_19's avatar
      cobus_19
      Frequent Visitor
      9 years ago

      This was perfect! Thank you very much!

      • cobus_19's avatar
        cobus_19
        Frequent Visitor
        9 years ago

        If I may introduce a further complexity:
        This gives me a great daily summary, however how would I set about including some other columns from my main table?

        For example, what if I want to see a breakdown of [category] per day. How do I introduce this without breaking the daily counts?

         

        So my current table output is

        Origin Date | Status 1 Count | Status 2 Count | etc

        date1 | 10 | 20
        date2 | 5| 30

        etc

         

        My desired output is:
        Origin Date | Category | Status 1 Count | Status 2 Count
        date1 | category1 | 5 | 15
        date1 | category2 | 5 | 5

        date2 | category1 | 1 | 10

        date2 | category2 | 2 | 10
        date2 | category3 | 3 | 10

         

        Your help is much appreciated.

         

        Regards
        Cobus