Home Forums Power Pivot Complex Categorical Calculated Column or Measure

This topic contains 4 replies, has 2 voices, and was last updated by  nyctophile 2 weeks, 5 days ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #10053

    nyctophile
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    Hi All,

    New here and have a complicated challenge with categorical data.

    I work alot with personnel data, so alot of names, organizations, locations, stuff like that. Here is my challenge:

    I have a spreadsheet with Class names(Ex. DAX101), Class Status(Completed/Enrolled/Dropped…) and Student Names.

    What I want to do is created a new column that finds students who have completed a set of classes and label them something like “PreRequisites_Met” with values “Yes”, “No”. I can’t figure out if I can use DAX or a calculated column. My challenge is each row represents one class that a student has completed, so there are multiple instances of each student. Some example rows would be

    Class/Status/Student_Name

    1)DAX101/Complete/Fred

    2)PowerPivot202/Complete/Fred

    3)DataModeling300/Complete/Barney

    3)Advanced Analysis/Enrolled/Barney

     

    How would I iterate through this data and find all students who have completed a set of classes? For example, say DAX101 and PowerPivot202 are the pre-requisites for Data Modeling. How to iterate through them and create a new column named “Pre-Requisites_Met” with values of “Yes” or “No”. In this case it would find Fred and have “Yes” for the new column value. Anything will help. I tried some things like

    PreReqs_Met := IF(AND(Class=”Dax101″ && Status=”Complete”,  && Class=”PowerPivot202″ && Status=”Complete”),”Yes”,”No”), but the issue there is the Class can’t be both Dax101 and PowerPivot202 in the same line, so how to iterate through by Student Name to check they’ve met all PreReq conditions? Thanks!

     

    See attached file for some simple example data. It’s from a MAC, but I also have Windows Excel 2016 with Power Pivot

    Attachments:
    You must be logged in to view attached files.
    #10055

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2308
    • Total: 2317

    Please see attached Excel 2016 workbook.

    Excel 2016 is required for this solution.

    Attachments:
    You must be logged in to view attached files.
    #10076

    nyctophile
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    Hi Tom,

    Thank you for that. I tried it multiple ways on my file, but got errors with the count formula.

    Any idea on doing this as a calculated column? I just want a column called “PreReq_Met” with values “Yes” or “No”, so I can filter students by that column that finished the pre-req list of classes. Thank you!

    #10081

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2308
    • Total: 2317

    What type of errors? Incorrect totals or error messages? If messages, what did the message say?

    Did you build the data model as shown, with a separate table for students, or were the tables in your data model organized in some other way?

    #10105

    nyctophile
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    I got the pre-req part of the code to work–it tagged the right people as meeting the requirements, but the count part didn’t poplulate tag the ones that met the pre-req with a 1, they were all zeros.

    My tables are set up as a Students and a Class table and related by a Student ID.

    Really though, I want to slice/filter by the pre-req met column, not count how many have met it, so is there a way to do this as a calculated column, so I can slice by it?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.