Forum Discussion
Count status across 3 tables - WITHOUT appending
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!
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_DataSetData 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
2 Replies
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.
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_DataSetData 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
