topic DAX FILTER with multiple criteria in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-FILTER-with-multiple-criteria/m-p/3145790#M112529 <P>Hello,</P><P>&nbsp;</P><P>This has been challenging to figure out.&nbsp; I have tableA with ITEMs / Category / Sales.&nbsp; I want add a column "Groups" to that table based on another table below.&nbsp; lookup to category and then where the sales number fits between MIN and MAX columns.&nbsp; Is that possible?</P><P>&nbsp;</P><P>tableA</P><P>ITEMs</P><TABLE><TBODY><TR><TD>Item</TD><TD>Category</TD><TD>Sales</TD></TR><TR><TD>OTH-10</TD><TD>AC_CAPS</TD><TD>0</TD></TR><TR><TD>OTH-12</TD><TD>AC_CAPS</TD><TD>10</TD></TR><TR><TD>F-30</TD><TD>AC_FTG</TD><TD>0</TD></TR><TR><TD>H-22</TD><TD>AUXCOOL</TD><TD>0</TD></TR><TR><TD>H-23</TD><TD>AUXCOOL</TD><TD>1</TD></TR><TR><TD>H-24</TD><TD>AUXCOOL</TD><TD>26</TD></TR><TR><TD>H-25</TD><TD>AUXCOOL</TD><TD>101</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>tableB</P><TABLE><TBODY><TR><TD>Groups</TD><TD>Category</TD><TD>Min</TD><TD>Max</TD></TR><TR><TD>Unranked</TD><TD>AC_CAPS</TD><TD>0</TD><TD>0</TD></TR><TR><TD>A - Rank 1-25</TD><TD>AC_CAPS</TD><TD>1</TD><TD>25</TD></TR><TR><TD>Unranked</TD><TD>AC_FTG</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Unranked</TD><TD>AUXCOOL</TD><TD>0</TD><TD>0</TD></TR><TR><TD>A - Rank 1-25</TD><TD>AUXCOOL</TD><TD>1</TD><TD>25</TD></TR><TR><TD>B - Rank 26-100</TD><TD>AUXCOOL</TD><TD>26</TD><TD>100</TD></TR><TR><TD>C - Rank 101-250</TD><TD>AUXCOOL</TD><TD>101</TD><TD>250</TD></TR></TBODY></TABLE><P>&nbsp;</P><DIV><DIV><SPAN>RankGroup = </SPAN></DIV><DIV><SPAN>CALCULATE</SPAN><SPAN>( </SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>( </SPAN><SPAN>tableB</SPAN><SPAN>[Groups]</SPAN><SPAN>, </SPAN><SPAN>BLANK</SPAN><SPAN>() ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>( </SPAN><SPAN>ALL</SPAN><SPAN>( </SPAN><SPAN>'tableB'</SPAN><SPAN> ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>tableA</SPAN><SPAN>[Category]</SPAN><SPAN> = tableB</SPAN><SPAN>[Category]</SPAN><SPAN> &amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; tableA</SPAN><SPAN>[Sales]</SPAN><SPAN> &gt;= tableB</SPAN><SPAN>[Min]</SPAN><SPAN> &amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; tableA</SPAN><SPAN>[Sales]</SPAN><SPAN> &lt; tableB</SPAN><SPAN>[Max]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>)</SPAN></DIV></DIV> Tue, 21 Mar 2023 21:24:19 GMT ybicalways79 2023-03-21T21:24:19Z DAX FILTER with multiple criteria https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-FILTER-with-multiple-criteria/m-p/3145790#M112529 <P>Hello,</P><P>&nbsp;</P><P>This has been challenging to figure out.&nbsp; I have tableA with ITEMs / Category / Sales.&nbsp; I want add a column "Groups" to that table based on another table below.&nbsp; lookup to category and then where the sales number fits between MIN and MAX columns.&nbsp; Is that possible?</P><P>&nbsp;</P><P>tableA</P><P>ITEMs</P><TABLE><TBODY><TR><TD>Item</TD><TD>Category</TD><TD>Sales</TD></TR><TR><TD>OTH-10</TD><TD>AC_CAPS</TD><TD>0</TD></TR><TR><TD>OTH-12</TD><TD>AC_CAPS</TD><TD>10</TD></TR><TR><TD>F-30</TD><TD>AC_FTG</TD><TD>0</TD></TR><TR><TD>H-22</TD><TD>AUXCOOL</TD><TD>0</TD></TR><TR><TD>H-23</TD><TD>AUXCOOL</TD><TD>1</TD></TR><TR><TD>H-24</TD><TD>AUXCOOL</TD><TD>26</TD></TR><TR><TD>H-25</TD><TD>AUXCOOL</TD><TD>101</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>tableB</P><TABLE><TBODY><TR><TD>Groups</TD><TD>Category</TD><TD>Min</TD><TD>Max</TD></TR><TR><TD>Unranked</TD><TD>AC_CAPS</TD><TD>0</TD><TD>0</TD></TR><TR><TD>A - Rank 1-25</TD><TD>AC_CAPS</TD><TD>1</TD><TD>25</TD></TR><TR><TD>Unranked</TD><TD>AC_FTG</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Unranked</TD><TD>AUXCOOL</TD><TD>0</TD><TD>0</TD></TR><TR><TD>A - Rank 1-25</TD><TD>AUXCOOL</TD><TD>1</TD><TD>25</TD></TR><TR><TD>B - Rank 26-100</TD><TD>AUXCOOL</TD><TD>26</TD><TD>100</TD></TR><TR><TD>C - Rank 101-250</TD><TD>AUXCOOL</TD><TD>101</TD><TD>250</TD></TR></TBODY></TABLE><P>&nbsp;</P><DIV><DIV><SPAN>RankGroup = </SPAN></DIV><DIV><SPAN>CALCULATE</SPAN><SPAN>( </SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>( </SPAN><SPAN>tableB</SPAN><SPAN>[Groups]</SPAN><SPAN>, </SPAN><SPAN>BLANK</SPAN><SPAN>() ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>( </SPAN><SPAN>ALL</SPAN><SPAN>( </SPAN><SPAN>'tableB'</SPAN><SPAN> ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>tableA</SPAN><SPAN>[Category]</SPAN><SPAN> = tableB</SPAN><SPAN>[Category]</SPAN><SPAN> &amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; tableA</SPAN><SPAN>[Sales]</SPAN><SPAN> &gt;= tableB</SPAN><SPAN>[Min]</SPAN><SPAN> &amp;</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; tableA</SPAN><SPAN>[Sales]</SPAN><SPAN> &lt; tableB</SPAN><SPAN>[Max]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>)</SPAN></DIV></DIV> Tue, 21 Mar 2023 21:24:19 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-FILTER-with-multiple-criteria/m-p/3145790#M112529 ybicalways79 2023-03-21T21:24:19Z Re: DAX FILTER with multiple criteria https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-FILTER-with-multiple-criteria/m-p/3146088#M112546 <P>RankGroup=MAXX(FILTER(TableB,TableB[Category]=TableA[Category]&amp;&amp;TableB[Min]&lt;=TableA[Sales]&amp;&amp;TableB[Max]&gt;=TableA[Sales]),TableB[Groups])</P> Wed, 22 Mar 2023 01:32:44 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-FILTER-with-multiple-criteria/m-p/3146088#M112546 wdx223_Daniel 2023-03-22T01:32:44Z Re: DAX FILTER with multiple criteria https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-FILTER-with-multiple-criteria/m-p/3148170#M112705 <P>Thanks!&nbsp; Worked perfectly</P> Wed, 22 Mar 2023 20:25:06 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-FILTER-with-multiple-criteria/m-p/3148170#M112705 ybicalways79 2023-03-22T20:25:06Z