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

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?