Forum Discussion
Helper IIIMatch values in seperate tables
Hi Locco
Using your sample data and adding a Date Table with the following formula
Dates = ADDCOLUMNS(CALENDAR("2019-01-01", "2020-12-31"), "Month", format([Date], "MMMM"), "MonthIndex", MONTH([Date]), "Year", YEAR([Date]))related to to the Fact table on the date, you can create the following formulas
Task Complete = if(COUNTROWS('Tasks_Complete') = BLANK(), 0, 1) Task Incomplete = if(Tasks_Complete[Task Complete] = 0, 1, 0)now if you bring Month from the Date Table and User from the User Table onto the visual along with the measure task complete and task incomplete with a little filtering you will see the following
Link to sample pbix, sample.pbix
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Helper IIIYes, everyone has a unique 5 digit ID number and I'm using that instead of the name. I'm only using it as a relationship really since the names on both list are not exactly the same and the ID's are.
These two "input tables" are representations of my dataset.
One table has all of the information regarding the tasks in it. This includes the Name/ID of the person, the date completed, the specific task, and the month.
(I have added other columns for each month of the year, if a person has completed the task that month then they have "yes" in that month and a "no" if not, these don't have to be used I was just thinking I could get it to work using that route, which was the reason for the previous measure I was trying to use.)
The second table is a list of all employees.
Input tables
Table 1
| E_ID | Date | Task | Month |
| 11111 | 01/01/2020 | aaa | January |
| 22222 | 01/07/2020 | bbb | January |
| 33333 | 01/25/2020 | ccc | January |
| 44444 | 02/01/2020 | ddd | February |
| 55555 | 02/25/2020 | eee | February |
| 66666 | 02/05/2020 | fff | February |
| 77777 | 02/17/2020 | ggg | February |
| 88888 | 03/01/2020 | hhh | March |
| 99999 | 03/01/2020 | iii | March |
| 10101 | 03/21/2020 | jjj | March |
Table 2
| E_ID |
| 11111 |
| 22222 |
| 33333 |
| 44444 |
| 55555 |
| 66666 |
| 77777 |
| 88888 |
| 99999 |
| 10101 |
I am trying to create 2 seperate 'table' visualizations. One for Completed, one for Not Completed as well as a Month filter. When "January" is selected the output on the visualizations would be
| Completed Visual |
| E_ID |
| 11111 |
| 22222 |
| 33333 |
| Not Completed Visual |
| E_ID |
| 44444 |
| 55555 |
| 66666 |
| 77777 |
| 88888 |
| 99999 |
| 10101 |
If "February" was selected in the Month filter the output on the visualization tables would be
| Completed Visual |
| E_ID |
| 22222 |
| 11111 |
| 66666 |
| 55555 |
| Not Completed Visual |
| E_ID |
| 33333 |
| 44444 |
| 77777 |
| 88888 |
| 99999 |
| 101010 |
I feel like I'm not doing a good job explaining this, my apologies if so.
Community ChampionOk, thanks for that. Let me give it a go and I'll let you know if I get stuck on anything.