topic Re: Subtracting Grouped Rows in Different Tables in DAX Commands and Tips

Subtracting Grouped Rows in Different Tables

Anonymous — Fri, 21 Aug 2020 16:36:18 GMT

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

Re: Subtracting Grouped Rows in Different Tables

amitchandak — Fri, 21 Aug 2020 16:11:21 GMT

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")

Re: Subtracting Grouped Rows in Different Tables

Anonymous — Fri, 21 Aug 2020 16:32:37 GMT

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

Re: Subtracting Grouped Rows in Different Tables

saraMissBI — Fri, 21 Aug 2020 16:47:02 GMT

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!

Re: Subtracting Grouped Rows in Different Tables

Anonymous — Fri, 21 Aug 2020 17:20:31 GMT

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

Re: Subtracting Grouped Rows in Different Tables

Anonymous — Fri, 21 Aug 2020 18:37:31 GMT

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

Re: Subtracting Grouped Rows in Different Tables

saraMissBI — Fri, 21 Aug 2020 20:36:47 GMT

Hi Anonymous ,

Sorry here is the link it should work for you now

Thank you