Forum Discussion

Locco's avatar
Locco
Helper 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!

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

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)

richbenmintz's avatar
richbenmintz
Resident Rockstar
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
    Helper III
    5 years ago

    This worked and was very simple. I definitely over thought all of this.

     

    Thank you all so much for your help!