Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm managing construction locations and I have 2 tables. Here are a few sample columns.
Table 1 (Locations)
Work Order # | Work Location Tag |
7970386 | A |
7970386 | B |
7970386 | C |
7970387 | A |
7970387 | B |
... | ... |
Table 2 (Summary)
Work Order # | Total Construction Locations |
7970386 | 372 |
7970387 | 106 |
7970388 | 271 |
... | ... |
My objective is to find out how many locations haven't started yet, which would follow the logic "Total Construction Locations - # of Work Locations per WO #". So basically if a location has a Work Location Tag, I assume assume it has started.
I would like to add this column next to the Total Construction Locations so I can eventually compare Total vs Not Started vs In Progress.
Thanks in advance for your help.
Robyn
Solved! Go to Solution.
Hey all,
Thanks for the help. I ended up figuring out the solution. Here's the equation below...
In Progress/Complete Locations = countx(filter('Table1', 'Table1'[Work Order #] = 'Table 2'[Work Order #]), 'Table1'[# of Locations Complete/In Progress])
Not Started Locations = Total Construction Locations - In Progress/Complete Locations
Hi @Anonymous
Please find here a pbix file with the solution to your problem.
If it solves the issue, please give Kudos and mark as solution.
Thanks!
Hello @saraMissBI ,
Unfortunately the file is stored at alakhawayn365-my.sharepoint.com and I can't access it. Can you post it differently or I can PM you my email address.
Robyn
Hey all,
Thanks for the help. I ended up figuring out the solution. Here's the equation below...
In Progress/Complete Locations = countx(filter('Table1', 'Table1'[Work Order #] = 'Table 2'[Work Order #]), 'Table1'[# of Locations Complete/In Progress])
Not Started Locations = Total Construction Locations - In Progress/Complete Locations
@Anonymous , Not very clear
New column in Table 2
if(isblank(Countx(filter(Table1,table1[Work Order #] =table2[Work Order #]),[Work Location Tag])),"Not Started","In Progress")
Hello @amitchandak ,
Apologies. I am looking for a number value.
For example if I have a WO with 21 Total Construction Locations from Table 2 and I have 5 Locations listed in Table 1 under that WO, I would like a column in Table 2 to say 16 Locations Not Started for that WO.
Robyn
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |