locked
Filtering across multiple related tables RRS feed

  • Question

  • Hi everyone,

    I would very much appreciate your help. I'm just an Excel user excited by the possibilities of PowerPivot for Excel and DAX, and have just started to work with it.

    I am currently trying to set up a small employee training database for internal trainings which works out quite well so far. This is its simplified data model:

    Now, in the table "Certifications", we want to record each person's status with each course. In it, I would like to have a calculated column (Certifications[LAST ATTENDANCE]), with the date on which the person last attended the course .

    The filtering I am looking for has to "AND" fields from multiple tables:

      • From Attendance, get all records related to Person-ID AND Course-ID    (-> seems to work with RELATEDTABLE(Attendance))
      • Filter these records:
        • Attendance[AttendanceStatus] = "attended"           (-> we also record if persons cancelled their attendance)
      • Filter the results further:
      • Get only the most recent date             (-> stored in Events[Date])

    I keep failing to concoct a formula that outputs this single date.

    Can anyone help me with this?

    Thank you for your time!




    • Edited by QOSIT_SAS Tuesday, September 12, 2017 11:47 AM Mentioned EXCEL in question, to make clear I have limited possibilities
    Monday, September 11, 2017 11:31 AM

Answers

  • Hi Willson,

    thank you again for your time!

    I have finally put together a formula which gives me the desired result. It is actually quite a simple one, maybe I have overcomplicated things:

    • I "dragged" Events[Date] into the Attendance-table (by VLOOKUP in Excel, but a RELATED(Events[Date])-column works, too.
    • Then I created this calculated column called "EventDate" in Certifications:
    =maxx(RELATEDTABLE(Attendance);if([AttendanceStatus]="attended";[EventDate];blank()))

    RELATEDTABLE honors both directions of the relationships and returns only the records with both related Person-ID AND Course-ID, which I find quite impressive, given that those two foreign keys are in different tables.

    The only thing I'm missing is the output "not yet attended" but I think that won't work anyway because the column is formatted as date. I can live with that or maybe create another calculated column for that.

    Could I ask one last question?

    I am getting the feeling that PowerPivot isn't the right tool for what I'm doing? I know it is strong with analyzing numbers, but I am working mostly with text here. I would normally have used an Access-DB, but we preferred an Excel-only solution.

    So when I learnt that Excel can work with relations now, I gave it a go and started building a relational database with it. Is this a somewhat unusual usage or do you see this often?

    Thank you again and best regards!


    • Edited by QOSIT_SAS Thursday, September 14, 2017 4:10 PM
    • Marked as answer by QOSIT_SAS Friday, September 15, 2017 6:40 AM
    Thursday, September 14, 2017 4:10 PM

All replies

  • Hi QOSIT_SAS,

    Thanks for your question.

    According to your description, you want to analyze certifications in this model, right?

    With the current relationship in your model, it is really hard to create calculated column (Certifications[LAST ATTENDANCE]). In your scenario, I would suggest you to change the relationship for your model.

    Building a fact table certification table to contain Event-ID,Attendance-ID,Course-ID and Person-ID, and all the dim table(Courses,Events,Attendance,Persons) are all connected to the fact table with those ID,then you can easily get what you want to analyze certifications.


    Best Regards
    Willson Yuan
    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

    Tuesday, September 12, 2017 1:51 AM
  • Hi Willson,

    thank you for your input!

    I hope I understand you correctly - if so, I feel that constructing such a fact table involves filling in redundant data. I forgot to mention that I am working only with Excel as front and back end. This means filling the tables is done by a human and a few formulas, not by a program routine.

    I want to analyze certifications, but also Events and Attendance. In my understanding, Events and Attendance are also fact tables. They have more fields than shown in the diagram, I just left them out to make it simpler.

    Usage examples:

    • To schedule a Course, you add an entry to "Events". Other fields are "Teacher", "EventStatus" and others. For example, later you will change its status from "planned" to "held" or "cancelled".
      In future, we want to analyze which and how many courses have been held or cancelled, for example.
    • For each event, you record its attendees in "Attendance". Their status could be "invited", "declined", "attended", "absent".
      Later, we want to analyze who got more or fewer opportunities to take courses, who declined or missed a lot of courses and other things.
    • In "Certifications" you will record which status each person has on a course. An employee can ask for a course, but it must be approved by his/her manager. Status will be "requested", "unapproved", "approved" etc. If approved, we will schedule the person for a course event (in the Attendance table).
      After attending, he/she is "certified" for this course for a certain time (e.g. 2 years) before it needs to be retaken. That is why I need the most recent attendance date calculated somewhere.

    Example for records in "Certifications" (mock-up in Excel):

    Is there any other way to get the desired result?

    Thank you very much!

    Tuesday, September 12, 2017 11:45 AM
  • Hi QOSIT_SAS,

    Thanks for your response.

    I have tried my best, but with the current relationship in your model, I can not get the desired result either.

    If there is any other way to do this, I am willing to learn the solution.


    Best Regards
    Willson Yuan
    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, September 13, 2017 7:25 AM
  • Hi Willson,

    thank you again for your time!

    I have finally put together a formula which gives me the desired result. It is actually quite a simple one, maybe I have overcomplicated things:

    • I "dragged" Events[Date] into the Attendance-table (by VLOOKUP in Excel, but a RELATED(Events[Date])-column works, too.
    • Then I created this calculated column called "EventDate" in Certifications:
    =maxx(RELATEDTABLE(Attendance);if([AttendanceStatus]="attended";[EventDate];blank()))

    RELATEDTABLE honors both directions of the relationships and returns only the records with both related Person-ID AND Course-ID, which I find quite impressive, given that those two foreign keys are in different tables.

    The only thing I'm missing is the output "not yet attended" but I think that won't work anyway because the column is formatted as date. I can live with that or maybe create another calculated column for that.

    Could I ask one last question?

    I am getting the feeling that PowerPivot isn't the right tool for what I'm doing? I know it is strong with analyzing numbers, but I am working mostly with text here. I would normally have used an Access-DB, but we preferred an Excel-only solution.

    So when I learnt that Excel can work with relations now, I gave it a go and started building a relational database with it. Is this a somewhat unusual usage or do you see this often?

    Thank you again and best regards!


    • Edited by QOSIT_SAS Thursday, September 14, 2017 4:10 PM
    • Marked as answer by QOSIT_SAS Friday, September 15, 2017 6:40 AM
    Thursday, September 14, 2017 4:10 PM
  • Hi QOSIT_SAS,

    Thanks for sharing the solution. I am glad to know that you have fixed this issue by yourself.

    I am getting the feeling that PowerPivot isn't the right tool for what I'm doing? I know it is strong with analyzing numbers, but I am working mostly with text here. I would normally have used an Access-DB, but we preferred an Excel-only solution.

    So when I learnt that Excel can work with relations now, I gave it a go and started building a relational database with it. Is this a somewhat unusual usage or do you see this often?

    For this question, instead of using PowerPivot, i would suggest you to use SSAS Tabular model 2016 and Power BI , as there are lots of new features PowerPivot does not support, you can just use excel as a report client.

    For more information about new features about SSAS 2016 and Power BI, please refer to below documents:

    https://docs.microsoft.com/en-us/sql/analysis-services/what-s-new-in-analysis-services
    https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-getting-started/


    Best Regards
    Willson Yuan
    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

    Friday, September 15, 2017 7:12 AM