topic Measure using columns from two tables (not directly related) in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-using-columns-from-two-tables-not-directly-related/m-p/2502017#M69183 <P>Hello all,</P><P>newbie here.</P><P>It looks like I bit off more than I can chew.</P><P>I have three tables. Item - list of all possible components with their specifications. Stock_item - available amount of components and Product_item - lists of components needed for assembling different products. Item has one to many relation to both Stock_item and Product_item (one beeing on Item). Sample data below.</P><P>I need to calculate how many products of selected type I can manufacture from available components.</P><P>So I need to divide available amount by necessary amount for every component needed for selected product and get their minimum.</P><P>&nbsp;</P><P>Sample data:</P><TABLE border="1"><TBODY><TR><TD>Item</TD><TD>...</TD></TR><TR><TD>S4 - 001</TD><TD>...</TD></TR><TR><TD>S4 - 002</TD><TD>...</TD></TR><TR><TD>TX - 040</TD><TD>...</TD></TR><TR><TD>TX - 030</TD><TD>...</TD></TR><TR><TD>TX - 029</TD><TD>...</TD></TR><TR><TD>TX - 053</TD><TD>...</TD></TR><TR><TD>Z4 - 013</TD><TD>...</TD></TR><TR><TD>TX - 044</TD><TD>...</TD></TR><TR><TD>TX - 038</TD><TD>...</TD></TR><TR><TD>TX - 042</TD><TD>...</TD></TR><TR><TD>TX - 034</TD><TD>...</TD></TR><TR><TD>TX - 033</TD><TD>...</TD></TR><TR><TD>TX - 058</TD><TD>...</TD></TR><TR><TD>TX - 084</TD><TD>...</TD></TR><TR><TD>TX - 003</TD><TD>...</TD></TR><TR><TD>TX - 076</TD><TD>...</TD></TR><TR><TD>TX - 033</TD><TD>...</TD></TR><TR><TD>TX - 058</TD><TD>...</TD></TR><TR><TD>TX - 060</TD><TD>...</TD></TR><TR><TD>D4 - 005</TD><TD>...</TD></TR><TR><TD>D4 - 006</TD><TD>...</TD></TR><TR><TD>D4 - 007</TD><TD>...</TD></TR><TR><TD>TX - 048</TD><TD>...</TD></TR><TR><TD>TX - 050</TD><TD>...</TD></TR><TR><TD>TX - 051</TD><TD>...</TD></TR></TBODY></TABLE><P>&nbsp;</P><TABLE border="1"><TBODY><TR><TD>StockItems</TD><TD>Amount</TD></TR><TR><TD>S4 - 001</TD><TD>450</TD></TR><TR><TD>S4 - 002</TD><TD>500</TD></TR><TR><TD>TX - 040</TD><TD>380</TD></TR><TR><TD>TX - 053</TD><TD>360</TD></TR><TR><TD>Z4 - 013</TD><TD>150</TD></TR><TR><TD>TX - 044</TD><TD>100</TD></TR><TR><TD>TX - 038</TD><TD>250</TD></TR><TR><TD>TX - 042</TD><TD>360</TD></TR><TR><TD>TX - 033</TD><TD>150</TD></TR><TR><TD>D4 - 005</TD><TD>100</TD></TR><TR><TD>TX - 084</TD><TD>250</TD></TR><TR><TD>TX - 076</TD><TD>175</TD></TR><TR><TD>TX - 030</TD><TD>320</TD></TR><TR><TD>TX - 058</TD><TD>360</TD></TR><TR><TD>TX - 060</TD><TD>150</TD></TR><TR><TD>D4 - 006</TD><TD>100</TD></TR><TR><TD>D4 - 007</TD><TD>250</TD></TR><TR><TD>TX - 048</TD><TD>100</TD></TR><TR><TD>TX - 050</TD><TD>250</TD></TR></TBODY></TABLE><P>&nbsp;</P><TABLE border="1"><TBODY><TR><TD>ProductItems</TD><TD>Amount</TD><TD>Product</TD></TR><TR><TD>S4 - 001</TD><TD>5</TD><TD>A</TD></TR><TR><TD>S4 - 002</TD><TD>3</TD><TD>A</TD></TR><TR><TD>TX - 040</TD><TD>15</TD><TD>A</TD></TR><TR><TD>TX - 030</TD><TD>11</TD><TD>A</TD></TR><TR><TD>D4 - 006</TD><TD>2</TD><TD>A</TD></TR><TR><TD>D4 - 007</TD><TD>8</TD><TD>A</TD></TR><TR><TD>Z4 - 013</TD><TD>8</TD><TD>A</TD></TR><TR><TD>TX - 044</TD><TD>4</TD><TD>A</TD></TR><TR><TD>S4 - 001</TD><TD>8</TD><TD>B</TD></TR><TR><TD>TX - 084</TD><TD>3</TD><TD>B</TD></TR><TR><TD>TX - 040</TD><TD>15</TD><TD>B</TD></TR><TR><TD>TX - 033</TD><TD>11</TD><TD>B</TD></TR><TR><TD>TX - 058</TD><TD>8</TD><TD>B</TD></TR><TR><TD>TX - 084</TD><TD>6</TD><TD>B</TD></TR><TR><TD>TX - 003</TD><TD>4</TD><TD>B</TD></TR><TR><TD>TX - 076</TD><TD>10</TD><TD>C</TD></TR><TR><TD>TX - 033</TD><TD>5</TD><TD>C</TD></TR><TR><TD>TX - 058</TD><TD>8</TD><TD>C</TD></TR><TR><TD>TX - 060</TD><TD>8</TD><TD>C</TD></TR><TR><TD>D4 - 005</TD><TD>4</TD><TD>C</TD></TR><TR><TD>D4 - 006</TD><TD>8</TD><TD>C</TD></TR><TR><TD>D4 - 007</TD><TD>5</TD><TD>C</TD></TR><TR><TD>TX - 048</TD><TD>3</TD><TD>C</TD></TR><TR><TD>TX - 050</TD><TD>7</TD><TD>C</TD></TR><TR><TD>S4 - 001</TD><TD>8</TD><TD>C</TD></TR></TBODY></TABLE> Mon, 09 May 2022 12:35:06 GMT Anonymous 2022-05-09T12:35:06Z Measure using columns from two tables (not directly related) https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-using-columns-from-two-tables-not-directly-related/m-p/2502017#M69183 <P>Hello all,</P><P>newbie here.</P><P>It looks like I bit off more than I can chew.</P><P>I have three tables. Item - list of all possible components with their specifications. Stock_item - available amount of components and Product_item - lists of components needed for assembling different products. Item has one to many relation to both Stock_item and Product_item (one beeing on Item). Sample data below.</P><P>I need to calculate how many products of selected type I can manufacture from available components.</P><P>So I need to divide available amount by necessary amount for every component needed for selected product and get their minimum.</P><P>&nbsp;</P><P>Sample data:</P><TABLE border="1"><TBODY><TR><TD>Item</TD><TD>...</TD></TR><TR><TD>S4 - 001</TD><TD>...</TD></TR><TR><TD>S4 - 002</TD><TD>...</TD></TR><TR><TD>TX - 040</TD><TD>...</TD></TR><TR><TD>TX - 030</TD><TD>...</TD></TR><TR><TD>TX - 029</TD><TD>...</TD></TR><TR><TD>TX - 053</TD><TD>...</TD></TR><TR><TD>Z4 - 013</TD><TD>...</TD></TR><TR><TD>TX - 044</TD><TD>...</TD></TR><TR><TD>TX - 038</TD><TD>...</TD></TR><TR><TD>TX - 042</TD><TD>...</TD></TR><TR><TD>TX - 034</TD><TD>...</TD></TR><TR><TD>TX - 033</TD><TD>...</TD></TR><TR><TD>TX - 058</TD><TD>...</TD></TR><TR><TD>TX - 084</TD><TD>...</TD></TR><TR><TD>TX - 003</TD><TD>...</TD></TR><TR><TD>TX - 076</TD><TD>...</TD></TR><TR><TD>TX - 033</TD><TD>...</TD></TR><TR><TD>TX - 058</TD><TD>...</TD></TR><TR><TD>TX - 060</TD><TD>...</TD></TR><TR><TD>D4 - 005</TD><TD>...</TD></TR><TR><TD>D4 - 006</TD><TD>...</TD></TR><TR><TD>D4 - 007</TD><TD>...</TD></TR><TR><TD>TX - 048</TD><TD>...</TD></TR><TR><TD>TX - 050</TD><TD>...</TD></TR><TR><TD>TX - 051</TD><TD>...</TD></TR></TBODY></TABLE><P>&nbsp;</P><TABLE border="1"><TBODY><TR><TD>StockItems</TD><TD>Amount</TD></TR><TR><TD>S4 - 001</TD><TD>450</TD></TR><TR><TD>S4 - 002</TD><TD>500</TD></TR><TR><TD>TX - 040</TD><TD>380</TD></TR><TR><TD>TX - 053</TD><TD>360</TD></TR><TR><TD>Z4 - 013</TD><TD>150</TD></TR><TR><TD>TX - 044</TD><TD>100</TD></TR><TR><TD>TX - 038</TD><TD>250</TD></TR><TR><TD>TX - 042</TD><TD>360</TD></TR><TR><TD>TX - 033</TD><TD>150</TD></TR><TR><TD>D4 - 005</TD><TD>100</TD></TR><TR><TD>TX - 084</TD><TD>250</TD></TR><TR><TD>TX - 076</TD><TD>175</TD></TR><TR><TD>TX - 030</TD><TD>320</TD></TR><TR><TD>TX - 058</TD><TD>360</TD></TR><TR><TD>TX - 060</TD><TD>150</TD></TR><TR><TD>D4 - 006</TD><TD>100</TD></TR><TR><TD>D4 - 007</TD><TD>250</TD></TR><TR><TD>TX - 048</TD><TD>100</TD></TR><TR><TD>TX - 050</TD><TD>250</TD></TR></TBODY></TABLE><P>&nbsp;</P><TABLE border="1"><TBODY><TR><TD>ProductItems</TD><TD>Amount</TD><TD>Product</TD></TR><TR><TD>S4 - 001</TD><TD>5</TD><TD>A</TD></TR><TR><TD>S4 - 002</TD><TD>3</TD><TD>A</TD></TR><TR><TD>TX - 040</TD><TD>15</TD><TD>A</TD></TR><TR><TD>TX - 030</TD><TD>11</TD><TD>A</TD></TR><TR><TD>D4 - 006</TD><TD>2</TD><TD>A</TD></TR><TR><TD>D4 - 007</TD><TD>8</TD><TD>A</TD></TR><TR><TD>Z4 - 013</TD><TD>8</TD><TD>A</TD></TR><TR><TD>TX - 044</TD><TD>4</TD><TD>A</TD></TR><TR><TD>S4 - 001</TD><TD>8</TD><TD>B</TD></TR><TR><TD>TX - 084</TD><TD>3</TD><TD>B</TD></TR><TR><TD>TX - 040</TD><TD>15</TD><TD>B</TD></TR><TR><TD>TX - 033</TD><TD>11</TD><TD>B</TD></TR><TR><TD>TX - 058</TD><TD>8</TD><TD>B</TD></TR><TR><TD>TX - 084</TD><TD>6</TD><TD>B</TD></TR><TR><TD>TX - 003</TD><TD>4</TD><TD>B</TD></TR><TR><TD>TX - 076</TD><TD>10</TD><TD>C</TD></TR><TR><TD>TX - 033</TD><TD>5</TD><TD>C</TD></TR><TR><TD>TX - 058</TD><TD>8</TD><TD>C</TD></TR><TR><TD>TX - 060</TD><TD>8</TD><TD>C</TD></TR><TR><TD>D4 - 005</TD><TD>4</TD><TD>C</TD></TR><TR><TD>D4 - 006</TD><TD>8</TD><TD>C</TD></TR><TR><TD>D4 - 007</TD><TD>5</TD><TD>C</TD></TR><TR><TD>TX - 048</TD><TD>3</TD><TD>C</TD></TR><TR><TD>TX - 050</TD><TD>7</TD><TD>C</TD></TR><TR><TD>S4 - 001</TD><TD>8</TD><TD>C</TD></TR></TBODY></TABLE> Mon, 09 May 2022 12:35:06 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-using-columns-from-two-tables-not-directly-related/m-p/2502017#M69183 Anonymous 2022-05-09T12:35:06Z Re: Measure using columns from two tables (not directly related) https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-using-columns-from-two-tables-not-directly-related/m-p/2502060#M69186 <P>Anonymous</LI-USER>&nbsp;Perhaps a column in your last table like the following. You could use the MIN by product to get the number of product that can be produced.</P> <LI-CODE lang="markup">Column = VAR __Item = 'Table3'[ProductItems] VAR __Amount = MAXX(FILTER('Table2',[StockItems] = __Item),[Amount]) RETURN DIVIDE(__Amount, [Amount])</LI-CODE> <P>&nbsp;</P> Mon, 09 May 2022 12:54:51 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-using-columns-from-two-tables-not-directly-related/m-p/2502060#M69186 Greg_Deckler 2022-05-09T12:54:51Z Re: Measure using columns from two tables (not directly related) https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-using-columns-from-two-tables-not-directly-related/m-p/2510979#M69645 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="313" data-lia-user-login="Greg_Deckler" class="lia-mention lia-mention-user">Greg_Deckler</a>&nbsp;Wow. Thank you. I'm not entirely sure what I'm doing yet, but it seems to work as it should.</P> Thu, 12 May 2022 15:21:18 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-using-columns-from-two-tables-not-directly-related/m-p/2510979#M69645 Anonymous 2022-05-12T15:21:18Z