Forum Discussion

WinterMist's avatar
WinterMist
Icon for Impactful Individual rankImpactful Individual
2 years ago

DAX Employee Count - Enrollment

Hello - 

 

The PBIX is shared here:

https://drive.google.com/file/d/1w9F1_rC4fzmVfxyFJjEYl_p2FwZVuqO2/view?usp=drive_link

 

This is a bit difficult to explain, but I will do my best:

- Measure [02 Employee Count - Contributions] counts the employees who actually donated each month.

- Measure [04 Employee Count - Enrollment] counts the employees who are actively enrolled to donate each month.

 

NOTE: The difference is subtle.  But the idea is that a user might enroll to give at the end of a month, without actually giving, since actual donations only get pulled on pay-days.  This frequently causes the results of these 2 measures to differ slightly by 1 or 2 each month, which is to be expected.

 

Data Problem 1) The data shows whether a Partner (i.e. Employer) is ACTIVE.  However, it does not give us an Inactive Date.

   - As a workaround, [03 IsPartnerActive] was created, that returns True/False based on whether Employee Donations exist.

   - For example, Partner XYZ was last ACTIVE in May 2024, but became INACTIVE in Jun 2024 based on Employee Donations.

 

 

 

 

 

 

Data Problem 2) The data has examples of Employees who are actively enrolled to donate, but in months where the employing Partner is no longer ACTIVE.  For example, the 12 UserIDs above are actively enrolled to donate in both May & June of 2024.  

- The report should COUNT them in May, because Partner XYZ was ACTIVE in May (due to the presence of Employee Donations).

- The report should NOT count them in June, because Partner XYZ was INACTIVE in Jun (due to Employee Donations being absent).

 

REPORT RESULTS:

- In the screenshot above, both of these things happen correctly because Partner is being sliced.

- However, if Partner is NOT sliced, then [04 Employee Count - Enrollment] shows wrong information starting in June.

   - Notice below that from May to June, [02 Employee Count - Contributions] CORRECTLY drops from 28 to 16. 

   - Notice however that from May to June, [04 Empoyee Count - Enrollment] INCORRECTLY stays at 28 in Jun, Jul & Aug.

 

NOTE: I understand partially that this is happening because [03 IsPartnerActive] is returning True due to the presence of Employee Donations in the other Partners when Partner is not being sliced.  However, I don't know how to fix this.

 

 

Any help would be greatly appreciated!

Nathan

3 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    You've identified the problem: IsPartnerActive isn't at the right granularity for a visual with multiple partners.

    So I threw this together to make a summarised table within the measure that you can then iterate (it only sums the Enrolment totals if the partner is active)

     

    VAR _testTab = SUMMARIZE('Dim Partner', 'Dim Partner'[PartnerID], "act", [05IsPartnerActive], "tots", SUM('Fact Enrollment'[Totals]))

VAR CalcTest = SUMX(_testTab, IF (TRUE() = [act], [tots], BLANK()))

    You would use this in the

    04 Employee Count - Enrollment =
    measure.
    Please test it (i make no guarantees because it was done quickly)

     

  • Olabanjik's avatar
    Olabanjik
    Frequent Visitor
    2 years ago

    Based on the Model view of your data, there is no relationship mapped between Fact Contribution and Fact Enrolment. I feel the structure of the model can be improved by either merging the two tables into one or creating a joining factor between the two tables such that enrolment table serves as the left join table. Then apply the dimensions consistently to ensure the filtering is appropriate.

     

    Aside that, my suggestion to resolve is to 

     

    •  Create 'IsActivePartner' check as a calculated column on Enrolment Table
    IsActivePartner = 
VAR a1 = [PartnerID]
VAR a2 = FORMAT([MonthYear], "yyyymm")
VAR sum_donation = CALCULATE(SUMX(ALLSELECTED('Fact Contributions'),  'Fact Contributions'[Amount]), 
'Fact Contributions'[PartnerID] = a1 , FORMAT('Fact Contributions'[PayDate], "yyyymm") = a2)
RETURN
 IF (sum_donation > 0, 1, 0)
    • Create the measure EmployeeEnrollment to count distinctenrolment  based on the new filter
    EmployeeEnrollment = 
    CALCULATE(
        DISTINCTCOUNT('Fact Enrollment'[UserId]),
        'Fact Enrollment'[IsActivePartner] = 1)

     

    Please mark as solution if this is helpful

  • WinterMist's avatar
    WinterMist
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    HotChilli & Olabanjik 

     

    Thank you very much for the help!

    Both of your solutions are working on a monthly basis.

    However, I am failing to get the "Total" column to return desirable data.

     

     

    Scenario 1

    - Year = 2024

    For the current Year, Total column should reflect enrollment as of the current month (TODAY()).

    The current month is October 2024, and there are no current enrollments (because there are no donations).

    Desired Result: Therefore the Total column should return 0.

     

    Scenario 2

    - Year = 2024

    - Month = April, May & June

    Total column should return the total from the LAST selected month (June 2024).

    Desired Result: In this case Total Column should equal 16.

     

    Thanks very much for your time.

    Nathan