Forum Discussion
Counting and Filtering Between Multiple Tables
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 ) ) + 0I'll await your response and see if you can validate the solution.
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:
- Identifies users who completed training (Training History table).
- Verifies they are allocated to the corresponding training make (Allocated Users table).
- 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
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:
- There is no "Make" column in the 'Training History' table.
- Cannot equate 'Training History' to 'Allocated Users' as they are not related
- 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.
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=04iw9SYI2HN80HKSPlease 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 ) ) + 0I'll await your response and see if you can validate the solution.