Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PKuipers
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

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

JobIDMachinePrintTime [h]
1G11
2G12
3G23
4G32
5G43
6G54
7G76
8G58
9G41

 and SeriesTasks

Job nrMachineUsed = RELATED(Machine)PrintTime = RELATED(PrintTime [h])
2G12
5G43
9G41

 

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?

1 ACCEPTED SOLUTION
tamerj1
Super User
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.

1.png

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

View solution in original post

5 REPLIES 5
tamerj1
Super User
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.

1.png

Total Print Time = 
SUMX (
    VALUES ( SeriesTasks[MachineUsed] ),
    VAR CurrentMachine = SeriesTasks[MachineUsed]
    RETURN
        CALCULATE ( 
            SUM ( AllTasks[PrintTime (h)] ),
            AllTasks[Machine] = CurrentMachine,
            ALL ( AllTasks )
        )
)
v-rongtiep-msft
Community Support
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)])))

vpollymsft_0-1673832811634.png

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.

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?

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
Super User
Super User

Hi @PKuipers 

please try

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors