topic Re: Counting Rows in Different Table based on Current Row Context in DAX Commands and Tips

Counting Rows in Different Table based on Current Row Context

Namdu_PAU — Wed, 20 Nov 2024 21:47:10 GMT

Hey all,

First of all, thank you for taking your time to stop by and reading my help request!
I am an intermediate user of Power BI and have started to encounter difficulties with more complex logic with DAX.

Task: I would like to calculate the % of individuals in each branch that have completed the training course. To do that, I need to divide the # users completed by the # users allocated for training.

Problem: I cannot figure out the DAX code to find # users allocated for training that respects the row context in the matrix while looking at a different table that is related 2 steps away.

For example, lets say the first row calculation for Adelaide - Expert - Cert 2

If i could write it in pseudocode, it'd be something like this:

COUNTIF( USER[Location] = "Adelaide" && USER[ALLOCATED] = "Cert 2") FROM All Allocated Users

However, the confusing part for me is that the relationships are not defined clearly.

I have a table with every user, and then another table allocating users to a MAKE

Then, I have a historical table for all training performed and another table explaining the structure of the training curriculum. This also has a MAKE field.

The image below is a snippet of the table, the last column is simply a Count of all the User IDS from the ALLOCATED USERS table and is returning the full list for every row in the table. I have tried many other DAX codes but cannot get it to simply count the number of users from the ALLOCATED USERS table.

Datastructure:

Allocated Users

Curriculum

Training History

Link to PBIx: (updated link)

https://www.dropbox.com/scl/fi/o3mjeaqyyukpb402lqhk7/PBI_Help_Counting-Users.pbix?rlkey=w52n2x3re2r0b5em6fb1g7fr3&st=2ngf8u85&dl=0

All help is sincerely appreciated and I will try my best to reciprocate in the community when I get better myself with Power BI.

Thank you all again!

Regards,

Namdu

Re: Counting Rows in Different Table based on Current Row Context

123abc — Wed, 20 Nov 2024 06:39:40 GMT

Msg on Drop Box

This item was deleted

You might be able to find it in your deleted files. If it's not there, try asking the person who shared it with you.

Re: Counting Rows in Different Table based on Current Row Context

123abc — Wed, 20 Nov 2024 06:41:31 GMT

please check your drop box setting ... i cant reach that. or may sahre your data on one_drive.

Re: Counting Rows in Different Table based on Current Row Context

Namdu_PAU — Wed, 20 Nov 2024 21:46:17 GMT

Hey 123abc,

That's so embarassing, i should have checked. I think I linked the previous version (which i deleted and updated).

https://www.dropbox.com/scl/fi/o3mjeaqyyukpb402lqhk7/PBI_Help_Counting-Users.pbix?rlkey=w52n2x3re2r0b5em6fb1g7fr3&st=8fwr0m93&dl=0

Re: Counting Rows in Different Table based on Current Row Context

Namdu_PAU — Thu, 21 Nov 2024 06:02:16 GMT

Okay, so as always, I have managed to work it out by myself after a few days.
I will write down the solution so anyone else who experiences similar problems can have something to hopefully guide them.

The Issue: I am a very visual person and like seeing mid-calculations in tables. This is why I created the 'Results Table' using SUMMARIZE. I assumed that by creating this table all the previous relationships would still exist. However, what I believe is happening is that this new table is an entity on its own and therefore LOST the relationships with the other tables.

The Solution: I moved the SUMMARIZE dax code away from the new table and instead into its own measure. This kept the relationships between each of the other tables and allowed me to do a COUNTROWS between two tables.

# Allocated Techs =

COUNTROWS(

FILTER('Technician OEM Allocation',

'Technician OEM Allocation'[Trained OEM] IN DISTINCT(Curriculum[Training Make])

)

Final result below. For my desired calculation (percentage) I can simply do a visual calculation.