Forum Discussion

GarnseyG's avatar
GarnseyG
Icon for Helper I rankHelper I
6 years ago

Measure from two tables

I have one table containing the names of different tasks and a field called Window that define a number of days. Table is call Tasks and has three elements, Task, Job, Window.   A second table iden...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

GarnseyG , you should able to join both tables on task and should able to calculate.

For more help Can you share sample data and sample output.

GarnseyG's avatar
GarnseyG
Icon for Helper I rankHelper I
6 years ago

Not sure how to share my pbix file, or for that matter how to attach anything

  • GarnseyG's avatar
    GarnseyG
    Icon for Helper I rankHelper I
    6 years ago

    If this helps: Here is some sample data as an example

    Task Table:

    TaskProjectWindow
    ap112
    bp120
    cp115
    dp235
    ep242
    fp318
    gp38
    hp323
    ip321
    jp4

    14

     

     

    People table

     

    PersonTaskLastDateWorked
    1a5/9/2020
    1b4/24/2020
    1c5/3/2020
    1d5/9/2020
    1e5/7/2020
    2f4/24/2020
    2g5/4/2020
    2h4/29/2020
    2i5/18/2020
    2j5/19/2020
    3a5/4/2020
    3b5/9/2020
    4c5/9/2020
    4d4/21/2020
    4e5/18/2020
    4f5/10/2020
    4g5/7/2020
    5h4/21/2020
    5i5/5/2020
    5j5/11/2020

     

    So for task "A" with a 12 day window.  Using 5/18/2020 as today, 12 days ago is 5/6.  Person 1 worked 5/9 so he/she is inside the window (good) while person 3 is outside the window (Bad). Two people work, one good, so 50% for that task.

     

    I would use the same logic when doing it by project, or doing it by person.

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      2 tables, 1 measure...

       

      So, you might have some luck looking at Open Tickets. It's designed to deal with date intervals. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

       

      Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

      The most important parts are:
      1. Sample data as text, use the table tool in the editing bar
      2. Expected output from sample data
      3. Explanation in words of how to get from 1. to 2.

      • GarnseyG's avatar
        GarnseyG
        Icon for Helper I rankHelper I
        6 years ago

        I'm new enough to Power BI to say I really don't have a clue as to how to convert the code in the example you provided to work on my data, so don't know if it would work.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      You may download my PBI file from here.

      Hope this helps.

      • GarnseyG's avatar
        GarnseyG
        Icon for Helper I rankHelper I
        6 years ago

        Ashish,

         

        Thanks. It's a great start I think. It doesn't report task g as 0% and when I look at it by project rather than task it doesn't calculate correctly. However, I'm hoping this gives me enough of an idea to do what I need. I'll let you know.