Forum Discussion
Count status across 3 tables - WITHOUT appending
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
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_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