Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Locco
Helper III
Helper III

Match values in seperate tables

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

NameDateTaskMonth
Jim01/01/2020aaaJanuary
Bob01/07/2020bbbJanuary
Lisa01/25/2020cccJanuary
Bob02/01/2020dddFebruary
Jim02/25/2020eeeFebruary
Frank02/05/2020fffFebruary
Carl02/17/2020gggFebruary
James03/01/2020hhhMarch
Frank03/01/2020iiiMarch
Bob03/21/2020jjjMarch
Lisa03/10/2020kkkMarch

 

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.

1 ACCEPTED 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

 

richbenmintz_0-1600374769626.png

 

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!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

23 REPLIES 23
PaulDBrown
Community Champion
Community Champion

@Locco 

 

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...)

Result.JPG

 

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Locco
Helper III
Helper III

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.

Locco
Helper III
Helper III

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?

PaulDBrown
Community Champion
Community Champion

@Locco 

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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.

PaulDBrown
Community Champion
Community Champion

@Locco 

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)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

 

richbenmintz_0-1600374769626.png

 

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!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

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_IDDateTaskMonth
1111101/01/2020aaaJanuary
2222201/07/2020bbbJanuary
3333301/25/2020cccJanuary
4444402/01/2020dddFebruary
5555502/25/2020eeeFebruary
6666602/05/2020fffFebruary
7777702/17/2020gggFebruary
8888803/01/2020hhhMarch
9999903/01/2020iiiMarch
1010103/21/2020jjjMarch
    

 

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
Community Champion
Community Champion

@Locco 

Ok, thanks for that. Let me give it a go and I'll let you know if I get stuck on anything.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Locco
Helper III
Helper III

I noticed that my measure is not returning any 1's, only 0's.

 

PaulDBrown
Community Champion
Community Champion

@Locco 

Excuse my clumsiness, but what defines a "completed" status?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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.

 

Locco
Helper III
Helper III

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 )
 
I set a filter to the FALSE visual to only show items with 1 but nothing at all shows.
 
Any ideas?
v-stephen-msft
Community Support
Community Support

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] )
)

1.png

 

2.Combine the two tables into one table.

Table =
SUMMARIZE ( CROSSJOIN ( 'Name', 'Date' ), [Name], [Month], [Sort] )

2.png

 

3.Create a calculated column.

Task Completed =
IF (
    [Month]
        IN SUMMARIZE ( FILTER ( 'Task', [Name] = 'Table'[Name] ), [Month] ),
    "False",
    "True"
)

3.png

 

4.Let the Month column be sorted by the Sort column.

4.png

 

5.The result is as follows.

5.png

 

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.

Untitled picture1.png

 

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 )

Untitled picture.png

 

3.The results are as follows.

Untitled picture2.pngUntitled picture3.pngUntitled picture4.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.