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!

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

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

 

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?