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!
Community ChampionYou mention you've added an ID to clarify things. Can you please share sample data (or better yet, a sample PBIX file) and what the expected output would be? (Excel mockup).
I'm happy to give this a go, but would rather work on a sample dataset/PBIX file.
Helper IIIThanks Paul,
Since this is "confidential" data I'm not sure if I can get any better sample data than what is on the OP.
I can try to clean that up some and put it in a pbix/excel file if that would help though.
- PaulDBrown
Let me give it a go with what you posted originally. You did however mention that you added an ID? Is that relevant?
what would be great is if you could provide an example of what you expect to see based on the data you have provided (a table/matrix or whatever hacked in Excel for example to see the depiction of the expected result based on the sample data)
richbenmintzResident Rockstar
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!- Locco
This worked and was very simple. I definitely over thought all of this.
Thank you all so much for your help!
- Locco
Yes, 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.
- PaulDBrown
Ok, thanks for that. Let me give it a go and I'll let you know if I get stuck on anything.