Forum Discussion

Locco's avatar
Locco
Icon for Helper III rankHelper III
6 years ago
Solved

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 ...
Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    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's avatar
Locco
Icon for Helper III rankHelper III
6 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • Locco's avatar
    Locco
    Icon for Helper III rankHelper III
    6 years ago

    The data is what it is, I can't do anything to clean, modify, or add to the database. I just have to work with the data as is.

     

    There is no indicator of the task being completed.

     

    I figured though since I have a list of names, I could compare a list who completed the task to that list of names and return any not matching. Those would be the ones who did not complete the task.

     

    Are you saying this isn't going to be possible with the current state of my data?