cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate with the intersection of two columns as filter

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

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.

 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

 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(
)

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?

1 ACCEPTED SOLUTION
Super User

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)] ),
ALL ( AllTasks )
)
)``````
5 REPLIES 5
Super User

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)] ),
ALL ( AllTasks )
)
)``````
Community Support

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)])))``

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.

Frequent Visitor

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?

Community Support

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.

Super User

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