Calculate row data from two different tables on multi selected slicer value

[Anonymous]

Hello 

I do not have so much DAX knowledge to solve the following problem.

I have a matrix table that get data from two different tables with no relationship while a measurement create one base on Slicer value selection.

Table1

part number , Part Group,  Part Name, life time (H),  

0393948, Electric, Electric motor, 10000

5858585, Electric, Capacitor, 4000

5985959, Mechanic, rear Axel, 12000

5858587,Mechanic, front Axel, 12000

Table2

Machine ID, Daily Avg use(h), Last Engine hours

L123F5858,  6.25, 1241

F5985D23,  5.28, 3341

T5685G59, 1.45, 365

Slicer = Table2[MachineID)

I want to calculate the forecasting component change time based on the machine daily utilization and the different between )(part life time) - (current Engine hours))/(Daily Avg Use) and transform it to the date in the future.

I managed to get ALL working for one single slicer selection by using 3 measurements.

Hours Diff = VALUE(max('Table1'[Life time])) - LOOKUPVALUE('Table2'[Last Engine Hours];[Machine ID];LEFT(CONCATENATEX(ALLSELECTED('Table2');[Machine IDl]&",");LEN(CONCATENATEX(ALLSELECTED('Table2');[Machine ID]&","))-1))

Days to Target = (VALUE(max('Table1'[Life Time])) - LOOKUPVALUE('Table2'[Last Engine Hours];[

Machine ID];LEFT(CONCATENATEX(ALLSELECTED('Table2)');[Machine ID]&",");LEN(CONCATENATEX(ALLSELECTED('Table2');[Machine ID]&","))-1))) / (LOOKUPVALUE('Table2'[Daily Avg use];[Machine ID];LEFT(CONCATENATEX(ALLSELECTED('Table2');[Machine ID]&",");LEN(CONCATENATEX(ALLSELECTED('Table2');[Machine ID]&","))-1)))

Estimate Date = FORMAT(now()+[Days to Target];"mmm") & "-" & FORMAT(now()+[Days to Target];"YYYY")

My target and problem is to make it work to multiple selected slicer so every selection add the MACHINE ID to the table and make the similar calculation. When I add more than one value to the slicer I get Hours Diff = VALUE(max('Table1'[Life Time])

My expected result is:

Part Group , MAchine ID, Part Name,  Life Time (H),  Daily Avg (h), Last Engine Hours,  Hours Diff,  Days to Target, Estimated Date 

Mechanic     L123F5858,   rear Axel,        12000           6.25,                1241 ,                   10759,           1721,44            MAR 2022

                     F5985D23,   rear Axel,        12000          5.28,                 3341,                     8659,             1639.96

                     T5685G59,  rear Axel,         12000          1.45,                365,                        11635,           8024.13

                     L123F5858,   Front Axel,     12000           6.25,               1241 ,                     10759,           1721,44

                     F5985D23,   Front Axel,      12000          5.28,                3341,                     8659,              1639.96

                     T5685G59,  rear Axel,         12000          1.45,                365,                      11635,             8024.13

Electric        

                     L123F5858,  Electric motor, 10000         6.25,               1241 ,                     8759

                     F5985D23,  Electric motor, 10000           5.28,              3341,                      6659

                     T5685G59, Electric motor, 10000         1.45,                 365,                        9635

Thank you,

Lior

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

You can create a new table use CrossJoin( ) function:

CrossJoinTable = CROSSJOIN('Table1','Table2')

Then create three measures in this new table:

HoursDiff = SUM('CrossJoinTable'[life time (H)])-SUM('CrossJoinTable'[Last Engine hours])

daysToTarget = 'CrossJoinTable'[HoursDiff]/SUM('CrossJoinTable'[Daily Avg use(h)])

EstimateDate = FORMAT(now()+'CrossJoinTable'[daysToTarget],"mmm") & "-" & FORMAT(now()+'CrossJoinTable'[daysToTarget],"YYYY")

Then drag [Machine ID] column from this new table into the slicer visual. For more information, see attached .pbix file.

Best Regards,

Qiuyun Yu

[Anonymous]

Thank you  @v-qiuyu-msft for your solution.

In practice I did the same yesterday when I come a across NestedJoin "M" query from this blog 

https://blog.crossjoin.co.uk/2014/06/02/join-conditions-in-power-query-part-1/

After I managed to get all to same table I also added the new values to the table instead of using measurements.

Now I need to find out if it is better to do it in the Datasource with M or in the visualization with DAX as you suggested 🙂

Regards,

Lior