Count status across 3 tables - WITHOUT appending

[powerbinewbie26]

Hi there! I'm brand new to powerbi and have been doing my best to learn as I go - this is likely very easy but I can't wrap my head around the correct DAX expression. 

I have 3 tables (TABLE A + TABLE B + TABLE C - each is sourced from its own Sharepoint data set), each of the 3 tables have the following common columns:
- status

- final date

I'm looking to make a visual that counts all tasks across the 3 tables and then identifies how many tasks are outstanding based on the 'status' and ideally also by 'final date'. I can't use append because of the type of data/how the dashboard has already been set up so im hoping a dax measure will work for this. 

Any help would be greatly appreciated! 

[Syk]

You can create a measure to calculate between unrelated tables. You'd do something like this.

Task Count = count('Table'[task_id]) + count( 'Table2'[task_id])

While you CAN do this.. You probably shouldn't as this will be a nightmare to understand when filtering if there's not a proper data model set up. Ideally you will want to combine these task files, have a column that details which sharepoint site its coming from. This will help with some of the filtering/counting/measure simplicity. A messy data model means complex DAX. If you'd like, I can help you sort through the model.

[powerbinewbie26 · Accepted solution]

hi @Syk thank you so much! i followed your advice and have managed to create the status by type a lot easier with an appended data set. I would love your help sorting through my model though - could you help with this additional question:

I want to track outstanding tasks per dataset source based on due date/status.
Sheet: Appended_DataSet

Data Set Source	Due Date	Status
S1	16-Jul-23	In Progress
S2	1-Aug-23	In Progress
S3	17-Jul-23	Completed

What dax expression can I use with my appended data set to say something like this: In my appended DataSet count the rows in column 'DataSet Source' and calculate the # of rows(tasks) that are past the 'due date' and are not 'completed' - I ideally want to have them displayed on the visual cards