cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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 (
RETURN
CALCULATE (
)
)
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 (
RETURN
CALCULATE (
)
)
Community Support

Hi @PKuipers ,

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

Create a measure.

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:

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

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 ,

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors