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
    6 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'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's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
6 years ago

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.

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

    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's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      6 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
        Icon for Resident Rockstar rankResident Rockstar
        6 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!