topic Measure development in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-development/m-p/4170960#M165680 <P>Hi all,&nbsp;</P><P>&nbsp;</P><P>I am seeking support with creating a measure:&nbsp;</P><P>ACCEPTANCE CRITERIA:&nbsp;</P><P>calcualte the values in the project_details table from the total Value column when the realization start month is 2025. In the project details table i have multiple years. also the project must be in execution phase, information also stored in project details. Finally I need to apply the right exchange rate within the right year.&nbsp;</P><P>&nbsp;</P><P>PROBLEM STATEMENT:&nbsp;</P><P><SPAN>The&nbsp;</SPAN>project_details<SPAN>&nbsp;table contains project currencies that may vary.&nbsp;</SPAN>I need to find the correct exchange rate and convert the values to the appropriate currency using the<SPAN>&nbsp;</SPAN>exchange_rate<SPAN>&nbsp;</SPAN>table.</P><P>&nbsp;</P><P>MATERIALS:&nbsp;</P><P>i have three table:</P><OL><LI>project_details</LI></OL><TABLE border="1"><TBODY><TR><TD>Project Number</TD><TD>Realization Start Month</TD><TD>Phase</TD><TD>Project Currency&nbsp;</TD><TD>Total Value</TD></TR><TR><TD>002540</TD><TD>Jan 2025</TD><TD>Execution</TD><TD>EUR</TD><TD>25489</TD></TR><TR><TD>002536</TD><TD>Sep 2024</TD><TD>Ideation</TD><TD>GBP</TD><TD>98651651</TD></TR><TR><TD>002533</TD><TD>Sep 2024</TD><TD>Execution</TD><TD>USD</TD><TD>51984651</TD></TR><TR><TD>002492</TD><TD>Nov 2024</TD><TD>Execution</TD><TD>CHF</TD><TD>4684621</TD></TR><TR><TD>002488</TD><TD>Nov 2024</TD><TD>Ideation</TD><TD>EUR</TD><TD>30664202</TD></TR><TR><TD>002487</TD><TD>Jan 2025</TD><TD>Ideation</TD><TD>GBP</TD><TD>27395241,6</TD></TR><TR><TD>002486</TD><TD>Jan 2025</TD><TD>Execution</TD><TD>USD</TD><TD>24126281,2</TD></TR><TR><TD>002485</TD><TD>Jan 2025</TD><TD>Execution</TD><TD>CHF</TD><TD>20857320,8</TD></TR><TR><TD>002484</TD><TD>Sep 2024</TD><TD>Execution</TD><TD>EUR</TD><TD>17588360,4</TD></TR></TBODY></TABLE><OL><LI>ASP_versions</LI></OL><TABLE border="1"><TBODY><TR><TD>AOP Year &amp; Tier</TD><TD>Year</TD></TR><TR><TD>2023 Tier 0</TD><TD>2023</TD></TR><TR><TD>2023 Tier 0a</TD><TD>2023</TD></TR><TR><TD>2023 Tier 0b</TD><TD>2023</TD></TR><TR><TD>2024 Tier 0c</TD><TD>2024</TD></TR><TR><TD>2024 Tier 1</TD><TD>2024</TD></TR><TR><TD>2024 Tier 1a</TD><TD>2024</TD></TR><TR><TD>2025 Tier 1b</TD><TD>2025</TD></TR><TR><TD>2025 Tier 1c</TD><TD>2025</TD></TR><TR><TD>2025 Tier 2</TD><TD>2025</TD></TR></TBODY></TABLE><OL><LI>exchange_rate</LI></OL><TABLE border="1"><TBODY><TR><TD>Currency From</TD><TD>Currency To</TD><TD>Exchange Rate</TD><TD>Rate Period</TD></TR><TR><TD>EUR</TD><TD>CHF</TD><TD>1,1179</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>EUR</TD><TD>1,0000</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>GBP</TD><TD>0,8895</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>USD</TD><TD>1,1617</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>CHF</TD><TD>1,1860</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>EUR</TD><TD>1,0000</TD><TD>2025</TD></TR><TR><TD>EUR</TD><TD>GBP</TD><TD>0,8865</TD><TD>2025</TD></TR><TR><TD>EUR</TD><TD>USD</TD><TD>1,2210</TD><TD>2025</TD></TR><TR><TD>EUR</TD><TD>CHF</TD><TD>1,0951</TD><TD>2025</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>this is the current measure:&nbsp;</P><DIV><DIV><SPAN><STRONG>Total Values</STRONG> =</SPAN></DIV><DIV><SPAN>SUMX</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>project_details</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>project_details</SPAN><SPAN>[Realization Start Month]</SPAN><SPAN> &gt;= </SPAN><SPAN>DATE</SPAN><SPAN>(</SPAN><SPAN>2025</SPAN><SPAN>, </SPAN><SPAN>1</SPAN><SPAN>, </SPAN><SPAN>1</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>project_details</SPAN><SPAN>[Realization Start Month]</SPAN><SPAN> &lt;= </SPAN><SPAN>DATE</SPAN><SPAN>(</SPAN><SPAN>2025</SPAN><SPAN>, </SPAN><SPAN>12</SPAN><SPAN>, </SPAN><SPAN>31</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>project_details</SPAN><SPAN>[Phase]</SPAN><SPAN> = </SPAN><SPAN>"Execution"</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>VAR</SPAN> <SPAN>ProjectCurrency</SPAN><SPAN> = </SPAN><SPAN>project_details</SPAN><SPAN>[Project Currency]</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>VAR</SPAN> <SPAN>ExchangeRate</SPAN><SPAN> = </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>LOOKUPVALUE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>exchange_rates</SPAN><SPAN>[Exchange Rate]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>exchange_rates</SPAN><SPAN>[Rate Period]</SPAN><SPAN>, </SPAN><SPAN>[Selected Year ASP]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>exchange_rates</SPAN><SPAN>[Currency To]</SPAN><SPAN>, </SPAN><SPAN>ProjectCurrency</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>RETURN</SPAN> <SPAN>project_details</SPAN><SPAN>[Total Value]</SPAN><SPAN> * </SPAN><SPAN>ExchangeRate</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><SPAN><STRONG>Selected Year ASP</STRONG> = </SPAN></DIV><DIV><SPAN>VALUES</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ASP_versions</SPAN><SPAN>[Year]</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>FINAL RESULT:&nbsp;</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>I create a graph bar pulling the total value measure and have two filters&nbsp;</SPAN></DIV><OL><LI><SPAN>exchange_rate[Currency To] -- where I can toggle between different exchange rates&nbsp;</SPAN></LI><LI><SPAN>ASP_versions[AOP Year &amp; Tier] -- where I select the right Tier</SPAN></LI></OL></DIV></DIV></DIV> Tue, 24 Sep 2024 14:43:46 GMT Nazar11 2024-09-24T14:43:46Z Measure development https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-development/m-p/4170960#M165680 <P>Hi all,&nbsp;</P><P>&nbsp;</P><P>I am seeking support with creating a measure:&nbsp;</P><P>ACCEPTANCE CRITERIA:&nbsp;</P><P>calcualte the values in the project_details table from the total Value column when the realization start month is 2025. In the project details table i have multiple years. also the project must be in execution phase, information also stored in project details. Finally I need to apply the right exchange rate within the right year.&nbsp;</P><P>&nbsp;</P><P>PROBLEM STATEMENT:&nbsp;</P><P><SPAN>The&nbsp;</SPAN>project_details<SPAN>&nbsp;table contains project currencies that may vary.&nbsp;</SPAN>I need to find the correct exchange rate and convert the values to the appropriate currency using the<SPAN>&nbsp;</SPAN>exchange_rate<SPAN>&nbsp;</SPAN>table.</P><P>&nbsp;</P><P>MATERIALS:&nbsp;</P><P>i have three table:</P><OL><LI>project_details</LI></OL><TABLE border="1"><TBODY><TR><TD>Project Number</TD><TD>Realization Start Month</TD><TD>Phase</TD><TD>Project Currency&nbsp;</TD><TD>Total Value</TD></TR><TR><TD>002540</TD><TD>Jan 2025</TD><TD>Execution</TD><TD>EUR</TD><TD>25489</TD></TR><TR><TD>002536</TD><TD>Sep 2024</TD><TD>Ideation</TD><TD>GBP</TD><TD>98651651</TD></TR><TR><TD>002533</TD><TD>Sep 2024</TD><TD>Execution</TD><TD>USD</TD><TD>51984651</TD></TR><TR><TD>002492</TD><TD>Nov 2024</TD><TD>Execution</TD><TD>CHF</TD><TD>4684621</TD></TR><TR><TD>002488</TD><TD>Nov 2024</TD><TD>Ideation</TD><TD>EUR</TD><TD>30664202</TD></TR><TR><TD>002487</TD><TD>Jan 2025</TD><TD>Ideation</TD><TD>GBP</TD><TD>27395241,6</TD></TR><TR><TD>002486</TD><TD>Jan 2025</TD><TD>Execution</TD><TD>USD</TD><TD>24126281,2</TD></TR><TR><TD>002485</TD><TD>Jan 2025</TD><TD>Execution</TD><TD>CHF</TD><TD>20857320,8</TD></TR><TR><TD>002484</TD><TD>Sep 2024</TD><TD>Execution</TD><TD>EUR</TD><TD>17588360,4</TD></TR></TBODY></TABLE><OL><LI>ASP_versions</LI></OL><TABLE border="1"><TBODY><TR><TD>AOP Year &amp; Tier</TD><TD>Year</TD></TR><TR><TD>2023 Tier 0</TD><TD>2023</TD></TR><TR><TD>2023 Tier 0a</TD><TD>2023</TD></TR><TR><TD>2023 Tier 0b</TD><TD>2023</TD></TR><TR><TD>2024 Tier 0c</TD><TD>2024</TD></TR><TR><TD>2024 Tier 1</TD><TD>2024</TD></TR><TR><TD>2024 Tier 1a</TD><TD>2024</TD></TR><TR><TD>2025 Tier 1b</TD><TD>2025</TD></TR><TR><TD>2025 Tier 1c</TD><TD>2025</TD></TR><TR><TD>2025 Tier 2</TD><TD>2025</TD></TR></TBODY></TABLE><OL><LI>exchange_rate</LI></OL><TABLE border="1"><TBODY><TR><TD>Currency From</TD><TD>Currency To</TD><TD>Exchange Rate</TD><TD>Rate Period</TD></TR><TR><TD>EUR</TD><TD>CHF</TD><TD>1,1179</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>EUR</TD><TD>1,0000</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>GBP</TD><TD>0,8895</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>USD</TD><TD>1,1617</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>CHF</TD><TD>1,1860</TD><TD>2024</TD></TR><TR><TD>EUR</TD><TD>EUR</TD><TD>1,0000</TD><TD>2025</TD></TR><TR><TD>EUR</TD><TD>GBP</TD><TD>0,8865</TD><TD>2025</TD></TR><TR><TD>EUR</TD><TD>USD</TD><TD>1,2210</TD><TD>2025</TD></TR><TR><TD>EUR</TD><TD>CHF</TD><TD>1,0951</TD><TD>2025</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>this is the current measure:&nbsp;</P><DIV><DIV><SPAN><STRONG>Total Values</STRONG> =</SPAN></DIV><DIV><SPAN>SUMX</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>project_details</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>project_details</SPAN><SPAN>[Realization Start Month]</SPAN><SPAN> &gt;= </SPAN><SPAN>DATE</SPAN><SPAN>(</SPAN><SPAN>2025</SPAN><SPAN>, </SPAN><SPAN>1</SPAN><SPAN>, </SPAN><SPAN>1</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>project_details</SPAN><SPAN>[Realization Start Month]</SPAN><SPAN> &lt;= </SPAN><SPAN>DATE</SPAN><SPAN>(</SPAN><SPAN>2025</SPAN><SPAN>, </SPAN><SPAN>12</SPAN><SPAN>, </SPAN><SPAN>31</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>project_details</SPAN><SPAN>[Phase]</SPAN><SPAN> = </SPAN><SPAN>"Execution"</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>VAR</SPAN> <SPAN>ProjectCurrency</SPAN><SPAN> = </SPAN><SPAN>project_details</SPAN><SPAN>[Project Currency]</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>VAR</SPAN> <SPAN>ExchangeRate</SPAN><SPAN> = </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>LOOKUPVALUE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>exchange_rates</SPAN><SPAN>[Exchange Rate]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>exchange_rates</SPAN><SPAN>[Rate Period]</SPAN><SPAN>, </SPAN><SPAN>[Selected Year ASP]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>exchange_rates</SPAN><SPAN>[Currency To]</SPAN><SPAN>, </SPAN><SPAN>ProjectCurrency</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>RETURN</SPAN> <SPAN>project_details</SPAN><SPAN>[Total Value]</SPAN><SPAN> * </SPAN><SPAN>ExchangeRate</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><SPAN><STRONG>Selected Year ASP</STRONG> = </SPAN></DIV><DIV><SPAN>VALUES</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ASP_versions</SPAN><SPAN>[Year]</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>FINAL RESULT:&nbsp;</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>I create a graph bar pulling the total value measure and have two filters&nbsp;</SPAN></DIV><OL><LI><SPAN>exchange_rate[Currency To] -- where I can toggle between different exchange rates&nbsp;</SPAN></LI><LI><SPAN>ASP_versions[AOP Year &amp; Tier] -- where I select the right Tier</SPAN></LI></OL></DIV></DIV></DIV> Tue, 24 Sep 2024 14:43:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-development/m-p/4170960#M165680 Nazar11 2024-09-24T14:43:46Z Re: Measure development https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-development/m-p/4170990#M165681 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="712055" data-lia-user-login="Nazar11" class="lia-mention lia-mention-user">Nazar11</a>&nbsp;Hi! Try adjust the measure like:</P><P>&nbsp;</P><P>Total Values =<BR />SUMX(<BR />FILTER(<BR />project_details,<BR />YEAR(project_details[Realization Start Month]) = 2025 &amp;&amp;<BR />project_details[Phase] = "Execution"<BR />),<BR />VAR ProjectCurrency = project_details[Project Currency]<BR />VAR SelectedCurrencyTo = SELECTEDVALUE(exchange_rate[Currency To])<BR />VAR SelectedYear = SELECTEDVALUE(ASP_versions[Year])<BR />VAR ExchangeRate =<BR />LOOKUPVALUE(<BR />exchange_rate[Exchange Rate],<BR />exchange_rate[Currency From], ProjectCurrency,<BR />exchange_rate[Currency To], SelectedCurrencyTo,<BR />exchange_rate[Rate Period], SelectedYear<BR />)<BR />RETURN project_details[Total Value] * ExchangeRate<BR />)</P><P>&nbsp;</P><P>BBF</P> Tue, 24 Sep 2024 14:51:29 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-development/m-p/4170990#M165681 BeaBF 2024-09-24T14:51:29Z Re: Measure development https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-development/m-p/4173091#M165734 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="712055" data-lia-user-login="Nazar11" class="lia-mention lia-mention-user">Nazar11</a>&nbsp;,</P> <P>&nbsp;</P> <P>Thanks to&nbsp;BeaBF&nbsp; for their interest in this issue. I have some other thoughts to add:</P> <P>(1) We can create a table.</P> <LI-CODE lang="markup">project_details_2025 = FILTER( project_details, project_details[Realization Start Month] &gt;= DATE(2025, 1, 1) &amp;&amp; project_details[Realization Start Month] &lt;= DATE(2025, 12, 31) &amp;&amp; project_details[Phase] = "Execution" )</LI-CODE> <P>(2) We can create a measure.</P> <LI-CODE lang="markup">Measure = VAR _value = SUM ( 'project_details_2025'[Total Value] ) VAR _rate = CALCULATE ( SUM ( 'exchange_rate'[Exchange Rate] ), FILTER ( ALL ( 'exchange_rate' ), [Currency To] = SELECTEDVALUE ( 'exchange_rate'[Currency To] ) &amp;&amp; [Rate Period] = SELECTEDVALUE ( ASP_versions[Year] ) ) ) RETURN _value * _rate </LI-CODE> <P>(3)&nbsp;Then the result is as follows.</P> <P><img /></P> <P>Best Regards,</P> <P>Neeko Tang</P> <P>If this post  <STRONG><EM>helps</EM></STRONG>, then please consider <STRONG><EM>Accept it as the solution </EM></STRONG> to help the other members find it more quickly.&nbsp;</P> Wed, 25 Sep 2024 07:09:48 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-development/m-p/4173091#M165734 Anonymous 2024-09-25T07:09:48Z