Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a list of employee names in one table and I want to cross reference a list in another table to see if a task has been completed.
The tasks are due monthly, so someone might have 9 entries and another might have 3. What I tried doesn't work since it returns TRUE for a person with only 1 entry.
What I thought was best was to make a new table and have it include the employee name and months of the year. Each month would state TRUE if the task was completed for that person for that month.
I can't get that to work though, and I'm not sure if that's even the best method.
I don't know if I've done a good job explaining, and I can't share my data, but if anyone has any suggestions please let me know.
***Added better tables
Input tables
Table 1
Name | Date | Task | Month |
Jim | 01/01/2020 | aaa | January |
Bob | 01/07/2020 | bbb | January |
Lisa | 01/25/2020 | ccc | January |
Bob | 02/01/2020 | ddd | February |
Jim | 02/25/2020 | eee | February |
Frank | 02/05/2020 | fff | February |
Carl | 02/17/2020 | ggg | February |
James | 03/01/2020 | hhh | March |
Frank | 03/01/2020 | iii | March |
Bob | 03/21/2020 | jjj | March |
Lisa | 03/10/2020 | kkk | March |
Table 2
Name |
Jim |
Bob |
Lisa |
James |
Carl |
Frank |
Vicki |
Robert |
John |
Bobby |
For output, I'm trying to use a month filter to populate 2 seperate tables, one for completed and not completed. When I select "January" this would be the output if using the data above:
Completed |
Jim |
Bob |
Lisa |
Not Completed |
James |
Carl |
Frank |
Vicki |
Robert |
John |
Bobby |
If February
Completed |
Bob |
Jim |
Frank |
Carl |
Not Completed |
Lisa |
James |
Bobby |
Robert |
Vicki |
John |
Hopefully this is clearer.
Solved! Go to Solution.
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!
Proud to be a Super User!
OK, can you please check this outcome? It doesn't match what you posted as your example in your last post, but I couldn't really make out the criterium for the "not completed" for a particular month (you seemed to include some values, but not others, but there wasn't an obvious criterium...)
If this is not what you are expecting, can you explain which values should not be included in either of the table per month?
Furthermore, what would be the expected outcome if the same ID has tasks completed in different months?
Proud to be a Super User!
Paul on Linkedin.
Instead of True/False and 1/0 I tried a simple Yes/No.
Still can't display the visual...
Function 'SWITCH' does not support comparing values of type Text with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.
I'm still not making any headway on this and I really need some assistance.
I'm currently trying to get this measure to work and I'm not having any real success...
Measure = VAR __filter = SELECTEDVALUE( 'Observation'[Month] ) VAR __Isfilter = SWITCH( __filter, "January", SELECTEDVALUE( 'Observation'[January] ) = FALSE(), "February", SELECTEDVALUE( 'Observation'[February] ) = FALSE(), "March", SELECTEDVALUE( 'Observation'[March] ) = FALSE(), "April", SELECTEDVALUE( 'Observation'[April] ) = FALSE(), "May", SELECTEDVALUE( 'Observation'[May] ) = FALSE(), "June", SELECTEDVALUE( 'Observation'[June] ) = FALSE(), "July", SELECTEDVALUE( 'Observation'[July] ) = FALSE(), "August", SELECTEDVALUE( 'Observation'[August] ) = FALSE(), "September", SELECTEDVALUE( 'Observation'[September] ) = FALSE(), "October", SELECTEDVALUE( 'Observation'[October] ) = FALSE(), "November", SELECTEDVALUE( 'Observation'[November] ) = FALSE(), "December", SELECTEDVALUE( 'Observation'[December] ) = FALSE() ) RETURN IF( __Isfilter = True(), 1, 0 )
I tried replacing the "," after FALSE() with &&, now I have an error saying the visual can't be displayed. The details state I cannot compare text values with the type True/False. I changed the True/False column to Text, but this isn't working.
I then changed the TRUE/FALSE values to be 1/O instead, now I get a nother error saying I can't compare Text and Integer.
What can I do to get this to work?
You 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.
Proud to be a Super User!
Paul on Linkedin.
Thanks 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.
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)
Proud to be a Super User!
Paul on Linkedin.
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!
Proud to be a Super User!
This worked and was very simple. I definitely over thought all of this.
Thank you all so much for your help!
Hi @Locco,
Glad to Help
Proud to be a Super User!
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.
Ok, thanks for that. Let me give it a go and I'll let you know if I get stuck on anything.
Proud to be a Super User!
Paul on Linkedin.
I noticed that my measure is not returning any 1's, only 0's.
Excuse my clumsiness, but what defines a "completed" status?
Proud to be a Super User!
Paul on Linkedin.
No worries Paul, I'm sure I've created some confusion.
A "completed" status would be defined by the name being on "Table 1." All names on that list have completed the task.
I've created columns on the main task table for the months of the year. Each column returns TRUE or FALSE if a person completed the task that month.
What I'd like it a measure so if I select "January" in the filter the people with TRUE will show up in one visual and the people with FALSE will show in another. I don't want individual filters for each month.
I tried this measure:
Measure = VAR __filter = SELECTEDVALUE( 'Observation'[Month] )
VAR __Isfilter =
SWITCH(
__filter,
"January", SELECTEDVALUE( 'Observation'[January] ) = FALSE(),
"February", SELECTEDVALUE( 'Observation'[February] ) = FALSE(),
"March", SELECTEDVALUE( 'Observation'[March] ) = FALSE(),
"April", SELECTEDVALUE( 'Observation'[April] ) = FALSE(),
"May", SELECTEDVALUE( 'Observation'[May] ) = FALSE(),
"June", SELECTEDVALUE( 'Observation'[June] ) = FALSE(),
"July", SELECTEDVALUE( 'Observation'[July] ) = FALSE(),
"August", SELECTEDVALUE( 'Observation'[August] ) = FALSE(),
"September", SELECTEDVALUE( 'Observation'[September] ) = FALSE(),
"October", SELECTEDVALUE( 'Observation'[October] ) = FALSE(),
"November", SELECTEDVALUE( 'Observation'[November] ) = FALSE(),
"December", SELECTEDVALUE( 'Observation'[December] ) = FALSE()
)
RETURN IF( __Isfilter = True(), 1, 0 )
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.
Thanks 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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |