topic Switch() function not working for all categories in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Switch-function-not-working-for-all-categories/m-p/3200435#M116323 <P>I am using a switch function to display data in a certain order and tag certain values to measures.&nbsp; however, the result does not display the first and last category.&nbsp; Here is the DAX</P><DIV><DIV><SPAN>Report Value = (</SPAN><SPAN>IF</SPAN><SPAN> (</SPAN><SPAN>AND</SPAN><SPAN> (</SPAN><SPAN>MAX</SPAN><SPAN> ( Main[Is sum])=</SPAN><SPAN>2</SPAN><SPAN>,[Filtered]),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>BLANK</SPAN><SPAN> (), </SPAN><SPAN>SWITCH</SPAN><SPAN>(</SPAN><SPAN>MIN</SPAN><SPAN>(Main[Is sum]),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</SPAN><SPAN>1</SPAN><SPAN>, </SPAN><SPAN>[OpeningBalance]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</SPAN><SPAN>2</SPAN><SPAN>, </SPAN><SPAN>(</SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(Data[Debit/Credit Amount]))</SPAN><SPAN>, </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</SPAN><SPAN>3</SPAN><SPAN>,</SPAN><SPAN>(</SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(Data[Debit/Credit Amount]))</SPAN><SPAN>, </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</SPAN><SPAN>4</SPAN><SPAN>,</SPAN><SPAN>[ClosingBalance]</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)))</SPAN></DIV></DIV><P>________________________________________________________________________________</P><DIV><DIV><SPAN>Filtered = </SPAN><SPAN>ISFILTERED</SPAN><SPAN>(Submapping[Sub_grouping])</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>result is </SPAN></DIV><DIV><img /><P>Opening balance and Closing balance are not shown.&nbsp; Each category is defined in the Main table and mapped to same numbers in Is Sum column.&nbsp; The total appearing as row subtotal is on.</P><P>&nbsp;</P><P>any guidance is appreciated.</P></DIV></DIV> Fri, 21 Apr 2023 15:47:46 GMT Jk29297 2023-04-21T15:47:46Z Switch() function not working for all categories https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Switch-function-not-working-for-all-categories/m-p/3200435#M116323 <P>I am using a switch function to display data in a certain order and tag certain values to measures.&nbsp; however, the result does not display the first and last category.&nbsp; Here is the DAX</P><DIV><DIV><SPAN>Report Value = (</SPAN><SPAN>IF</SPAN><SPAN> (</SPAN><SPAN>AND</SPAN><SPAN> (</SPAN><SPAN>MAX</SPAN><SPAN> ( Main[Is sum])=</SPAN><SPAN>2</SPAN><SPAN>,[Filtered]),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>BLANK</SPAN><SPAN> (), </SPAN><SPAN>SWITCH</SPAN><SPAN>(</SPAN><SPAN>MIN</SPAN><SPAN>(Main[Is sum]),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</SPAN><SPAN>1</SPAN><SPAN>, </SPAN><SPAN>[OpeningBalance]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</SPAN><SPAN>2</SPAN><SPAN>, </SPAN><SPAN>(</SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(Data[Debit/Credit Amount]))</SPAN><SPAN>, </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</SPAN><SPAN>3</SPAN><SPAN>,</SPAN><SPAN>(</SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(Data[Debit/Credit Amount]))</SPAN><SPAN>, </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</SPAN><SPAN>4</SPAN><SPAN>,</SPAN><SPAN>[ClosingBalance]</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)))</SPAN></DIV></DIV><P>________________________________________________________________________________</P><DIV><DIV><SPAN>Filtered = </SPAN><SPAN>ISFILTERED</SPAN><SPAN>(Submapping[Sub_grouping])</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>result is </SPAN></DIV><DIV><img /><P>Opening balance and Closing balance are not shown.&nbsp; Each category is defined in the Main table and mapped to same numbers in Is Sum column.&nbsp; The total appearing as row subtotal is on.</P><P>&nbsp;</P><P>any guidance is appreciated.</P></DIV></DIV> Fri, 21 Apr 2023 15:47:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Switch-function-not-working-for-all-categories/m-p/3200435#M116323 Jk29297 2023-04-21T15:47:46Z Re: Switch() function not working for all categories https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Switch-function-not-working-for-all-categories/m-p/3200999#M116367 <P>Hello Jk,</P><P>&nbsp;</P><P><A href="https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/m-p/1626726#M32906" target="_self">Please help us help you</A>.</P> Sat, 22 Apr 2023 13:35:55 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Switch-function-not-working-for-all-categories/m-p/3200999#M116367 Wilson_ 2023-04-22T13:35:55Z Re: Switch() function not working for all categories https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Switch-function-not-working-for-all-categories/m-p/3201047#M116374 <P>Adding further information, the relationship of tables are:&nbsp;&nbsp;</P><P><img /></P><P>further, the table Main has the following columns and data:</P><P><img /></P><P>The table matrix output filters are :</P><P><img /></P><P>&nbsp;</P> Sat, 22 Apr 2023 15:01:21 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Switch-function-not-working-for-all-categories/m-p/3201047#M116374 Jk29297 2023-04-22T15:01:21Z