topic Re: Calculate with the intersection of two columns as filter in DAX Commands and Tips

Calculate with the intersection of two columns as filter

PKuipers — Fri, 13 Jan 2023 15:00:35 GMT

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?

Re: Calculate with the intersection of two columns as filter

tamerj1 — Fri, 13 Jan 2023 16:05:39 GMT

Hi PKuipers

please try

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

Re: Calculate with the intersection of two columns as filter

Anonymous — Mon, 16 Jan 2023 01:34:35 GMT

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.

Re: Calculate with the intersection of two columns as filter

PKuipers — Wed, 18 Jan 2023 09:50:13 GMT

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?

Re: Calculate with the intersection of two columns as filter

tamerj1 — Wed, 18 Jan 2023 11:42:07 GMT

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

Re: Calculate with the intersection of two columns as filter

Anonymous — Thu, 19 Jan 2023 00:59:42 GMT

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.