Forum Discussion

GarnseyG's avatar
GarnseyG
Helper 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
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, GarnseyG 

 

I'd like to suggest you create a measure as below.

Percenatge By task = 
var _tabperson = 
SUMMARIZE(
    People,
    People[Person],
    People[Task],
    People[LastDateWorked],
    "Window",
    var _task = People[Task]
    return
    LOOKUPVALUE(Task[Window],Task[Task],_task)
)
var _newtab = 
ADDCOLUMNS(
    _tabperson,
    "flag",
    IF(
        TODAY()-[Window]<=[LastDateWorked],
        1,0
    )
)
var numofgood = 
SUMX(
    FILTER(
        _newtab,
        [flag]=1
    ),
    [flag]
)

var result = 
numofgood/DISTINCTCOUNT(People[Person])
return
IF(
    ISBLANK(result),
    0,
    result
)

 

Today is 5/21/2020. Here is the result.

 

The pbix is attached in the end.

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • GarnseyG's avatar
    GarnseyG
    Helper I
    6 years ago

    Whow, most impressive! It's going to take some time to understand what you've done here, but the calculations look right at the task level. I'm going to see if I can figure out what you did and apply it to do it at the project level.

     

    I'll let you know.