Calculate with the intersection of two columns as filter

[PKuipers]

I have a model to calculate OEE statistics of a 3D printing plant and I'm trying to incorporate some service data (time spent on repairs).

The service duration needs to be divided by a ratio of time spent on series production and all time spent (including test production)

I have two tables, which have a 1:1 relation. I used the RELATED function to copy some data from the first table to the second

AllTasks <-> SeriesTasks

JobID     1:1    Job nr

Machine   => MachineUsed (column added with related)

PrintTime [h] => PrintTime (column added with related)

AllTasks contains a longer list of machines and jobs than SeriesTasks, but alle machines and Jobs of Series tasks can be found in AllTasks (as they are copied from that table). SeriesTasks comes from another source and contains a lot of other production information, so I cannot combine the tables.

e.g. AllTasks

JobID	Machine	PrintTime [h]
1	G1	1
2	G1	2
3	G2	3
4	G3	2
5	G4	3
6	G5	4
7	G7	6
8	G5	8
9	G4	1

 and SeriesTasks

Job nr	MachineUsed = RELATED(Machine)	PrintTime = RELATED(PrintTime [h])
2	G1	2
5	G4	3
9	G4	1

I'm trying to calculate to caculate the total print time of machines used for series production, something like:

CALCULATE(

    SUM('AllTasks'[PrintTime [h]]]),

    'AllTasks'[Machine] = 'SeriesTasks'[MachineUsed]

)

The above code does not work.

I have tried things with FILTER, TREATAS and CALCULATETABLE + INTERSECT as I found on this forum, but I can't get anything to work.

Any idea what I'm missing?

[tamerj1 · Accepted solution]

Hi @PKuipers 
Please refer to attached sample file with the proposed solution. In order to obtain correct total for the involved machines further DAX need to be added.

Total Print Time = 
SUMX (
    VALUES ( SeriesTasks[MachineUsed] ),
    VAR CurrentMachine = SeriesTasks[MachineUsed]
    RETURN
        CALCULATE ( 
            SUM ( AllTasks[PrintTime (h)] ),
            AllTasks[Machine] = CurrentMachine,
            ALL ( AllTasks )
        )
)

[Anonymous]

Hi @PKuipers ,

I have created a simple smaple, please refer to it to see if it helps you.

Create a measure.

Measure = CALCULATE(SUM(alltask[PrintTime [h]]]),FILTER(ALL(alltask),alltask[Machine]=SELECTEDVALUE('series task'[MachineUsed = RELATED(Machine)])))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[PKuipers]

This seems to be a good step into the right direction for me, thanks!

One part did not work for me, as the syntax was not accepted: 

'series task'[MachineUsed = RELATED(Machine)]

but I could work around that by adding a related colum that converted machine names, so I could simply apply:

Measure = CALCULATE(SUM(alltask[PrintTime [h]]]),FILTER(ALL(alltask),alltask[Machine]=SELECTEDVALUE('series task'[MachineUsed])))

It seems to work well to extract the correct total print time per used machine per month, but for the ones that are not used for series production, and for the sum per month, it returns 0.07.

The value might be because of some problem with my data, I can probably find that myself.

But why does the measure not sum the total print time of all printers, used for series production, per month? And what can I do to fix that?

[Anonymous]

Hi @tamerj1 ,

Please have a try.

Create another measure2 based on the [measure]

_measure2_ = 
var _b = SUMMARIZE('table','table'[job id],"aaa",'table'[measure])
return
IF(HASONEVALUE('table'[job id]),[measure],SUMX(_b,[aaa]))

Best Regards
Community Support Team _ Polly

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

[tamerj1]

Hi @PKuipers 

please try

Total Time (Calculated Column) =
CALCULATE (
SUM ( 'AllTasks'[PrintTime [h]]] ),
'AllTasks'[Machine] = 'SeriesTasks'[MachineUsed],
REMOVEFILTERS ()
)