Counting and Filtering Between Multiple Tables

[Namdu_PAU]

Hey PBI Community,

Intermediate user of PBI here and require some help once again.

I have been slowly digging away at creating this dashboard and I have realised that I have not been filtering my data properly in the calculations.

Context: I am summarising historical training data with different datasets for: Users, User Allocation, Training History and Training Curriculum. Each of the training modules are separated by a training Make.

Task: I want to create a measure that counts the number of users that:
a) Have completed the training (appear in the training history)

b) Is also allocated the same training make
c) Achieved 100% completion

My Problem:

I have a SUMMARIZE calculation with one of the columns counting every user in the training history. However it is counting EVERY user and I cannot filter it for users that also share the same make. My CALCULATE/FILTER function doesnt work as the two tables are not related in the schema. This results in the number of users who have completed the training to be greater than the actual number of allocated users (non-allocated users can still do the training).

I have been working on this problem for a few days now and tried looking through the forums for any similar examples but could not find any. Any help is sincerely appreciated.

My last thoughts are that I need to create a second SUMMARIZE table? or perhaps SUMX with SUMMARIZE? I feel like I am close to the solution but my limited knowledge of DAX is holding me back.

PBIx: https://www.dropbox.com/scl/fi/u0pjni6g1inz0sibl9owu/PBI_Help_Counting-Users_v3.pbix?rlkey=vatecpf3m... 

[Poojara_D12]

Hi @Namdu_PAU 

The challenge here lies in correctly filtering the data to align with your relational structure. Since your current data model does not directly relate the Allocated Users table with the Training History table via the Make field, we need to create a measure that bridges this gap effectively using DAX. Here's how to do it step by step:

Measure Logic

We need a measure that:

1. Identifies users who completed training (Training History table).
2. Verifies they are allocated to the corresponding training make (Allocated Users table).
3. Ensures they achieved 100% completion.

Proposed DAX Measure

Here's the DAX formula you can try:

Completed and Allocated Users = 
VAR AllocatedUsers = 
    SUMMARIZE(
        'Allocated Users',
        'Allocated Users'[User ID],
        'Allocated Users'[Make]
    )

RETURN
CALCULATE(
    COUNTROWS(AllocatedUsers),
    FILTER(
        AllocatedUsers,
        VAR CurrentMake = 'Allocated Users'[Make]
        RETURN
            CALCULATE(
                COUNTROWS('Training History'),
                'Training History'[User ID] = 'Allocated Users'[User ID] &&
                'Training History'[Make] = CurrentMake &&
                'Training History'[Percentage Completion] = 100
            ) > 0
    )
)

OR you can also try the other one:

Completed and Allocated Users = 
CALCULATE(
    DISTINCTCOUNT('Allocated Users'[User ID]), -- Count distinct allocated users
    FILTER(
        'Allocated Users',
        CALCULATE(
            COUNTROWS('Training History'),
            'Training History'[User ID] = 'Allocated Users'[User ID] &&
            'Training History'[Make] = 'Allocated Users'[Make] && 
            'Training History'[Percentage Completion] = 100
        ) > 0
    )
)

Also consider the below points:

• Relationships: Ensure that the necessary relationships exist between Allocated Users and Training History via the User ID and Make.
• Columns: Confirm that the columns User ID, Make, and Percentage Completion are correctly referenced.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[Namdu_PAU]

Hi Poojara,

Thank you so much for replying to my request!

May I kindly request some additional information on your proposed solutions? I just tried putting the code in for both suggestions and they do not appear to work.

There are few things which I think may be stopping it from working:

1. There is no "Make" column in the 'Training History' table.
2. Cannot equate 'Training History' to 'Allocated Users' as they are not related
3. There is no "Percentage Completion" measure or column

Also, I do not mean to be rude, but was this solution produced in Chat GPT? The response is structured eerily like an AI model and the code is reflective of it as well.

[Poojara_D12]

Hi @Namdu_PAU 

Thank you so much for the genuine feedback, it's not a AI response, as I didn't opened the PBIX file, I just saw the screenshot, also, I was working on a task as well so I guess I got mixed up.

Please allow me some time, will check all the attachments and will get back you you.

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[Namdu_PAU · Accepted solution]

Please accept my apologies Poojara! Then you are way too professional in your approach and managed to convinced me it was automated.

Sincerely, your time and help is much appreciated.

I was playing around with the filters and I believe I may have encountered something that has worked - but I do not really understand why.

The thing I changed was the FILTER for the table input for SUMMARIZE. I also changed the table from 'training history' to 'allocated users'.

# Allocated Users Completed = 

COUNTROWS(
    FILTER(
        SUMMARIZE(
            FILTER('Allocated Users','Allocated Users'[Make] IN DISTINCT(Curriculum[Make]))
            ,USERS[User - User ID]
            ,"Completed", CALCULATE(COUNTA('Training History'[User ID]),FILTER('Allocated Users','Allocated Users'[Make] IN DISTINCT(Curriculum[Make])))
            ,"Total # Courses", [Total Courses]
            ,"Percentage", DIVIDE(COUNTA('Training History'[User ID]),[Total Courses])
            )
        , [Percentage]=1
    )
) + 0

I'll await your response and see if you can validate the solution.