none
DAX Counting Frequency Across Rows RRS feed

  • Question

  • Hi all,

    I'm currently working with a fairly large data set, and am having some issues with devising a DAX formula that will count across rows once I pivot a column.

    For example, in my powerpivot data model I have the columns:

    ID         Course            Year

    123      MAT101           2000

    123      ENG101           2000

    456      SCI-101           2001

    456      BUS-12            2002

    456      ENG-110          2003

    In the data model I also have a "Headcount" measure.

    So when I transpose the data in the pivot window and put the Year column into "COLUMNS", the ID into "ROWS" and Headcount into "VALUES", the data are then displayed as:

    ID     Course    2000        2001        2002           2003

    123      MAT101     1

    123      ENG101     1

    456      SCI-101                1

    456      BUS-120                            1

    456      ENG-110                                                  1

    So what I'm looking to do is aggregate the values of 1 by ID, which would look like:

    ID      Count_By_ID

    123               2

    456               3

    And then be able to pull in the courses if I want to drill into which 2 or 3 are attributed to each ID.

    Hope this makes sense, and any help would be greatly appreciated, thanks!

    Darko

    Tuesday, March 28, 2017 11:55 PM

Answers

  • Hi Darko,

    You need to add another filter in your DAX. List the following formula as an example.

    measure-test:=CALCULATE(COUNTA(Test[Year]),ALLEXCEPT(Test,Test[ID]),FILTER(Test,OR(Test[Prog_Code]="SCI101",Test[Prog_Code]="MAT101",Test[Prog_Code]="ENG101"))

    Best Regards,
    Angelia



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 30, 2017 6:36 AM
    Moderator

All replies

  • Hi Darko,

    You can create a measure to get result without transposing the sample data.

    measure:=CALCULATE(COUNTA(Test[Year]),ALLEXCEPT(Test,Test[ID]))

    Click the Pivot Table on homepage, select ID as row level, and the measure as value level, please see the following screenshot.

    If you have other issues, please feel free to ask.

    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 29, 2017 2:26 AM
    Moderator
  • Thanks so much Angelia!

    I also have a few programs in one column in the data model (Prog_Code) I would like to exclude from the measure calculation, which looks something like this

    Prog_Code

    SCI101

    BUS101

    ENG101

    BUS101

    BUS101

    MAT101

    MAT101

    So for example, would there be a way to append a function to your measure saying "add up all of the enrolments/applications for only SCI101, MAT101 and ENG101"? Something like an "ALLEXCEPT" comes to mind, but not quite sure how I'd utilize that one. Hopefully that makes sense.

    Thanks again!

    Darko 

    Wednesday, March 29, 2017 6:36 PM
  • Hi Darko,

    You need to add another filter in your DAX. List the following formula as an example.

    measure-test:=CALCULATE(COUNTA(Test[Year]),ALLEXCEPT(Test,Test[ID]),FILTER(Test,OR(Test[Prog_Code]="SCI101",Test[Prog_Code]="MAT101",Test[Prog_Code]="ENG101"))

    Best Regards,
    Angelia



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 30, 2017 6:36 AM
    Moderator