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!
Hi Locco ,
My sample data are these.
Name | Date | Task | Month |
A | 2020-9-1 | 101 | Sep |
B | 2020-9-2 | 102 | Sep |
C | 2020-8-10 | 102 | Aug |
D | 2020-8-11 | 101 | Aug |
A | 2020-7-5 | 102 | Jul |
B | 2020-6-5 | 103 | Jun |
Name |
A |
B |
C |
D |
E |
F |
G |
1.Create a calendar table.
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
"Month", FORMAT ( [Date], "MMM" ),
"Sort", MONTH ( [Date] )
)
2.Combine the two tables into one table.
Table =
SUMMARIZE ( CROSSJOIN ( 'Name', 'Date' ), [Name], [Month], [Sort] )
3.Create a calculated column.
Task Completed =
IF (
[Month]
IN SUMMARIZE ( FILTER ( 'Task', [Name] = 'Table'[Name] ), [Month] ),
"False",
"True"
)
4.Let the Month column be sorted by the Sort column.
5.The result is as follows.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IIIThanks but I don't think this will work either. I seem to run into the same problem.
For example you have the name "G" in your list of names but not in the list of completed tasks. Even though name "G" has not completed any tasks for any month, in your sample data each month states "True."
I'll try to get a sample data book made since I cannot share my actual data.
Hi Locco ,
According to the sample data you provided, you can try the following method.
1.For displaying the name of the person who completed the task, you could directly use the Name column of Table1 as table visual. Because when you use Month column as a slicer to filter, Name will automatically filter.
2.For displaying the names of people who have not completed the task, you could try to use the Name column of Table2 as a table visual, create a measure and then drag it to Filters and set it.
Measure = IF ( MAX ( 'Table1'[Name] ) <> MAX ( 'Table2'[Name] ), 1 )3.The results are as follows.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- Locco
Stephen,
This works more than anything else has, but it isn't giving the desired results.
There are names which are not populating in either category.
For instance I am a person who has only completed a task in January (slacker), My name populates in the "Complete" visual when January is selected, but my name isn't in either visual once you select any other month. My name should populate in the "Not Complete" visual when I select those months and it isn't. I'm using mine as a test since I know which months I did/didn't complete.
***I've also had to stop using the "Name" column and instead use a # identifier. There were inaccuracies with the names which made matching impossible. Instead of a name I now use a 5 digit unique identifier.
Hi Locco ,
The sample data you gave does not indicate that the task has not been completed. If you can, please improve your sample data, I will do my best to help you, thank you.
Best regards,
Stephen Tao