topic Dax formula that does not filter on calender table in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-that-does-not-filter-on-calender-table/m-p/3457318#M131799 <P>I need to know how my DAX formula called "<SPAN>Fakturert" needs to be updated to filter on dimKalender.&nbsp;</SPAN></P><P>&nbsp;</P><P>I have 3 tables&nbsp;</P><P><img /></P><P>Relationship between&nbsp;<BR />JobTask[Job No_] --&gt;[JobLedgerEntry[Job No_]<BR />dimKalender[Date] --&gt;[JobLedgerEntry[Posting Date]</P><P>&nbsp;</P><P>In Power Bi report view in my table where I use JobTask[Job No_]&nbsp;</P><P>When I use dimKalender for filter it only filters correctly for one of my DAX formulas.&nbsp;</P><P>&nbsp;</P><P><img /></P><DIV><DIV><SPAN>Incorrectly for this Dax</SPAN></DIV><DIV><SPAN>Fakturert =</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>JobTask</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobTask</SPAN><SPAN>[Job Task Type]</SPAN><SPAN> = </SPAN><SPAN>4</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Line Amount (LCY)]</SPAN><SPAN>),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>ALL</SPAN><SPAN>(</SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job No_]</SPAN><SPAN> = </SPAN><SPAN>EARLIER</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Job No_]</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job Task No_]</SPAN><SPAN> &gt;= </SPAN><SPAN>EARLIER</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Totaling.1]</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job Task No_]</SPAN><SPAN> &lt;= </SPAN><SPAN>EARLIER</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Totaling.2]</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Source Code]</SPAN><SPAN> = </SPAN><SPAN>"SALG"</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>Correct for this DAX</SPAN></DIV></DIV><DIV><DIV><SPAN>FakturertLinje =</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>SelectedJobNo</SPAN><SPAN> = </SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Job No_]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>SelectedJobTaskNo</SPAN><SPAN> = </SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Job Task No_]</SPAN><SPAN>)</SPAN></DIV><BR /><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>SUMX</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job No_]</SPAN><SPAN> = </SPAN><SPAN>SelectedJobNo</SPAN><SPAN> &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job Task No_]</SPAN><SPAN> = </SPAN><SPAN>SelectedJobTaskNo</SPAN><SPAN> &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Source Code]</SPAN><SPAN> = </SPAN><SPAN>"SALG"</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Line Amount (LCY)]</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; )</SPAN></DIV><DIV>&nbsp;Dataset</DIV><DIV><img /><P>&nbsp;</P><P><A href="https://megasolutionsas-my.sharepoint.com/:f:/g/personal/rune_megasolutions_no/EpN3Bga1EJpBhB3EX_q7NqgBAJ72H_WK18buAMW109BDMQ?e=pIrE4f" target="_blank" rel="noopener">Link to pbix file</A></P></DIV></DIV> Tue, 03 Oct 2023 08:56:11 GMT Rune_ 2023-10-03T08:56:11Z Dax formula that does not filter on calender table https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-that-does-not-filter-on-calender-table/m-p/3457318#M131799 <P>I need to know how my DAX formula called "<SPAN>Fakturert" needs to be updated to filter on dimKalender.&nbsp;</SPAN></P><P>&nbsp;</P><P>I have 3 tables&nbsp;</P><P><img /></P><P>Relationship between&nbsp;<BR />JobTask[Job No_] --&gt;[JobLedgerEntry[Job No_]<BR />dimKalender[Date] --&gt;[JobLedgerEntry[Posting Date]</P><P>&nbsp;</P><P>In Power Bi report view in my table where I use JobTask[Job No_]&nbsp;</P><P>When I use dimKalender for filter it only filters correctly for one of my DAX formulas.&nbsp;</P><P>&nbsp;</P><P><img /></P><DIV><DIV><SPAN>Incorrectly for this Dax</SPAN></DIV><DIV><SPAN>Fakturert =</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>JobTask</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobTask</SPAN><SPAN>[Job Task Type]</SPAN><SPAN> = </SPAN><SPAN>4</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Line Amount (LCY)]</SPAN><SPAN>),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>ALL</SPAN><SPAN>(</SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job No_]</SPAN><SPAN> = </SPAN><SPAN>EARLIER</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Job No_]</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job Task No_]</SPAN><SPAN> &gt;= </SPAN><SPAN>EARLIER</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Totaling.1]</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job Task No_]</SPAN><SPAN> &lt;= </SPAN><SPAN>EARLIER</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Totaling.2]</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Source Code]</SPAN><SPAN> = </SPAN><SPAN>"SALG"</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>Correct for this DAX</SPAN></DIV></DIV><DIV><DIV><SPAN>FakturertLinje =</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>SelectedJobNo</SPAN><SPAN> = </SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Job No_]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>SelectedJobTaskNo</SPAN><SPAN> = </SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Job Task No_]</SPAN><SPAN>)</SPAN></DIV><BR /><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>SUMX</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job No_]</SPAN><SPAN> = </SPAN><SPAN>SelectedJobNo</SPAN><SPAN> &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job Task No_]</SPAN><SPAN> = </SPAN><SPAN>SelectedJobTaskNo</SPAN><SPAN> &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Source Code]</SPAN><SPAN> = </SPAN><SPAN>"SALG"</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Line Amount (LCY)]</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; )</SPAN></DIV><DIV>&nbsp;Dataset</DIV><DIV><img /><P>&nbsp;</P><P><A href="https://megasolutionsas-my.sharepoint.com/:f:/g/personal/rune_megasolutions_no/EpN3Bga1EJpBhB3EX_q7NqgBAJ72H_WK18buAMW109BDMQ?e=pIrE4f" target="_blank" rel="noopener">Link to pbix file</A></P></DIV></DIV> Tue, 03 Oct 2023 08:56:11 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-that-does-not-filter-on-calender-table/m-p/3457318#M131799 Rune_ 2023-10-03T08:56:11Z Re: Dax formula that does not filter on calender table https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-that-does-not-filter-on-calender-table/m-p/3457522#M131805 <P>Solutions<BR /><BR /></P><DIV><DIV><SPAN>Fakturert =</SPAN></DIV><DIV><SPAN>-</SPAN><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>JobTask</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobTask</SPAN><SPAN>[Job Task Type]</SPAN><SPAN> = </SPAN><SPAN>4</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Line Amount (LCY)]</SPAN><SPAN>),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>SUMMARIZE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job No_]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job Task No_]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Posting Date]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Source Code]</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job No_]</SPAN><SPAN> = </SPAN><SPAN>EARLIER</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Job No_]</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job Task No_]</SPAN><SPAN> &gt;= </SPAN><SPAN>EARLIER</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Totaling.1]</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Job Task No_]</SPAN><SPAN> &lt;= </SPAN><SPAN>EARLIER</SPAN><SPAN>(</SPAN><SPAN>JobTask</SPAN><SPAN>[Totaling.2]</SPAN><SPAN>) &amp;&amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>JobLedgerEntry</SPAN><SPAN>[Source Code]</SPAN><SPAN> = </SPAN><SPAN>"SALG"</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>)</SPAN></DIV></DIV> Tue, 03 Oct 2023 10:52:32 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-formula-that-does-not-filter-on-calender-table/m-p/3457522#M131805 Rune_ 2023-10-03T10:52:32Z