topic Re: COUNTAX DAX Formel in Power BI Desktop need explanation in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/COUNTAX-DAX-Formel-in-Power-BI-Desktop-need-explanation/m-p/3903715#M152104 <P>Hi&nbsp;&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="739300" data-lia-user-login="Vlad_Akka" class="lia-mention lia-mention-user">Vlad_Akka</a>&nbsp;,</P> <P>&nbsp;</P> <P>Measure and calculated columns have different row contexts, and the value of the calculated column is calculated based on the table context and is a fixed value.</P> <P>Refer to:</P> <P><A href="https://community.fabric.microsoft.com/t5/Desktop/Using-a-Measure-in-a-Calculated-Column/m-p/425985" target="_blank">Solved: Using a Measure in a Calculated Column - Microsoft Fabric Community</A></P> <P>&nbsp;</P> <P>You may consider the following formula for calculating column dax:</P> <LI-CODE lang="markup">Column = COUNTX( FILTER(ALL('Person List Lookup'), 'Person List Lookup'[CS Course number]=EARLIER('Cousrse Groups Lookup'[CS Course number])),'Person List Lookup'[Personal Nr])</LI-CODE> <P><img /></P> <P>This is the related document, you can view this content:</P> <P><A href="https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/gg413423(v=sql.110)?redirectedfrom=MSDN" target="_blank">Context in DAX Formulas | Microsoft Learn</A></P> <P><A href="https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/" target="_blank">Calculated Columns and Measures in DAX - SQLBI</A></P> <P><A href="https://learn.microsoft.com/en-us/dax/best-practices/dax-column-measure-references" target="_blank">Column and measure references in DAX - DAX | Microsoft Learn</A></P> <P>&nbsp;</P> <P>Best Regards,</P> <P>Liu Yang</P> <P>If this post <STRONG>helps</STRONG>, then please consider <EM>Accept it as the solution</EM> to help the other members find it more quickly.</P> Thu, 09 May 2024 01:40:21 GMT Anonymous 2024-05-09T01:40:21Z COUNTAX DAX Formel in Power BI Desktop need explanation https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/COUNTAX-DAX-Formel-in-Power-BI-Desktop-need-explanation/m-p/3902993#M152085 <P><STRONG>Hello all.</STRONG></P><P>&nbsp;</P><P>I have two Lookup tables. One of it has data about learning courses (number of course, Course Start data, the quantitiy of the participants etc). The second one is a person table with learning courses in which they have participated.</P><P>The both of the tables are exported from Excelsheets.</P><P>&nbsp;</P><P>"Cousrse Groups Lookup " Table</P><TABLE><TBODY><TR><TD>CS Course number</TD><TD>Date of Start</TD><TD>Date of End</TD><TD>Course activity state</TD><TD>Participants Quantitiy</TD><TD>Participants<BR />successful Quantitiy</TD><TD>KPI</TD></TR><TR><TD>1</TD><TD>04.09.2024</TD><TD>25.02.2024</TD><TD>ended</TD><TD>5</TD><TD>5</TD><TD>Not used now</TD></TR><TR><TD>2</TD><TD>27.03.2024</TD><TD>08.05.2024</TD><TD>ended</TD><TD>22</TD><TD>21</TD><TD>Not used now</TD></TR><TR><TD>3</TD><TD>15.05.2024</TD><TD>15.02.2025</TD><TD>active</TD><TD>6</TD><TD>0</TD><TD>Not used now</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>"Person List Lookup " Table</P><TABLE><TBODY><TR><TD>Personal Nr</TD><TD>Persons</TD><TD>CS Course number</TD><TD>Participation<BR />state</TD><TD>Country</TD></TR><TR><TD>1</TD><TD>Lionel</TD><TD>1</TD><TD>1</TD><TD>France</TD></TR><TR><TD>2</TD><TD>Laurence</TD><TD>1</TD><TD>1</TD><TD>France</TD></TR><TR><TD>3</TD><TD>François, Vetea</TD><TD>1</TD><TD>1</TD><TD>France</TD></TR><TR><TD>4</TD><TD>Sashko</TD><TD>1</TD><TD>1</TD><TD>France</TD></TR><TR><TD>5</TD><TD>Vetea</TD><TD>1</TD><TD>1</TD><TD>France</TD></TR><TR><TD>6</TD><TD>Schreiner</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>7</TD><TD>Schneider</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>8</TD><TD>Schindler</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>9</TD><TD>Backenbauer</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>10</TD><TD>Ivaschko</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>11</TD><TD>Lindnder</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>12</TD><TD>Musterman</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>13</TD><TD>Musterman1</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>14</TD><TD>Müller</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>15</TD><TD>Schmidt</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>16</TD><TD>Fischer</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>17</TD><TD>Weber</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>18</TD><TD>Meyer</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>19</TD><TD>Wagner</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>20</TD><TD>Becker</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>21</TD><TD>Schulz</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>22</TD><TD>Bauer</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>23</TD><TD>Schäfer</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>24</TD><TD>Koch</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>25</TD><TD>Schröder</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>26</TD><TD>Neumann</TD><TD>2</TD><TD>1</TD><TD>Germany</TD></TR><TR><TD>27</TD><TD>Zimmermann</TD><TD>2</TD><TD>2</TD><TD>Germany</TD></TR><TR><TD>28</TD><TD>Braun&nbsp;</TD><TD>3</TD><TD>3</TD><TD>Germany</TD></TR><TR><TD>29</TD><TD>Krüger</TD><TD>3</TD><TD>3</TD><TD>Germany</TD></TR><TR><TD>30</TD><TD>Hofmann</TD><TD>3</TD><TD>3</TD><TD>Germany</TD></TR><TR><TD>31</TD><TD>Klein</TD><TD>3</TD><TD>3</TD><TD>Germany</TD></TR><TR><TD>32</TD><TD>Schmitt</TD><TD>3</TD><TD>3</TD><TD>Germany</TD></TR><TR><TD>33</TD><TD>Bley</TD><TD>3</TD><TD>3</TD><TD>Germany</TD></TR><TR><TD>34</TD><TD>Schmitz</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>35</TD><TD>Krause</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>36</TD><TD>Schulze</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>37</TD><TD>Schuster</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>38</TD><TD>Köhler</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>39</TD><TD>König</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>40</TD><TD>Winter</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>41</TD><TD>Schach</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>42</TD><TD>Huber</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>43</TD><TD>Kaiser</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>44</TD><TD>Fuchs</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>45</TD><TD>Peters</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>46</TD><TD>Lang</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>47</TD><TD>Möller</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>48</TD><TD>Weiß</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>49</TD><TD>Jung</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>50</TD><TD>Hahn</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>51</TD><TD>Schubert</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>52</TD><TD>Vogel</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>53</TD><TD>Vogler</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>54</TD><TD>Keller</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>55</TD><TD>Berger</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>56</TD><TD>Winkler</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>57</TD><TD>Roth</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>58</TD><TD>Beck</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>59</TD><TD>Schumacher</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>60</TD><TD>Groß</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>61</TD><TD>Seidel</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>62</TD><TD>Ziegler</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>63</TD><TD>Kuhn</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>64</TD><TD>Bergmann</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>65</TD><TD>Pfeiffer</TD><TD>0</TD><TD>4</TD><TD>Germany</TD></TR><TR><TD>66</TD><TD>Novak</TD><TD>0</TD><TD>4</TD><TD>Czech Republic</TD></TR><TR><TD>67</TD><TD>Noak</TD><TD>0</TD><TD>4</TD><TD>Czech Republic</TD></TR><TR><TD>68</TD><TD>Pietsch</TD><TD>0</TD><TD>4</TD><TD>Czech Republic</TD></TR><TR><TD>69</TD><TD>Kowalski</TD><TD>0</TD><TD>4</TD><TD>Czech Republic</TD></TR></TBODY></TABLE><P>(no worry, all names are used for example)</P><P>&nbsp;</P><P>In the "Course Groups Lookup" table i have the column "Participants Quantitiy" yet. But it is calculated and prompt manually.</P><P>Every time if the new course begin, the neu column shall be added into the Table "Course Group Lookup" with recalculation, based on the data in the "Person List Lookup" Table. The both tables have one relationship 1 : x. See the figure below</P><P>&nbsp;</P><P>My goal is to add the column&nbsp; "Participants Quantitiy CC" with a measure, which just calculate the participants quantity for every learning course (see the figure).</P><P><img /></P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>Of course i could use the DAX Formula CALCULATE with FILTER and logical conditions. But in this case i shall add the logic if a new learning course begin and this is not what i want.</P><P>&nbsp;</P><P>If you will see attantively on the measure itself, and on the result in the added column, probably you will come to the same conclusion as i - it doesn´t work because i have nearly 30 persons with the course number "0" and due to the logic of the formula COUNTAX i shall have for all of the learning courses 30 persons. But y<SPAN class="">ou will be wery surprised because it works (see the measure below and the content of the column in the picture above).</SPAN></P><P>&nbsp;</P><P>EXAMPLE&nbsp;of&nbsp;Quantity&nbsp;=&nbsp; &nbsp;<STRONG><SPAN class="">COUNTAX</SPAN></STRONG><SPAN class="">&nbsp;(</SPAN>&nbsp;'Person&nbsp;List&nbsp;Lookup',&nbsp;<STRONG>'Person&nbsp;List&nbsp;Lookup'[CS&nbsp;Course&nbsp;number]&nbsp;=&nbsp;<SPAN class="">0</SPAN></STRONG>&nbsp;<SPAN class="">)&nbsp;</SPAN></P><P>&nbsp;</P><P><SPAN class="">There is no matter if i compare with 0 or change the condition to less as 2. The result will be the same - it works as expected. </SPAN></P><P>&nbsp;</P><P><SPAN class="">&nbsp;Truly says, i have no idea, but i will be appraciate to you to hear your explanations.</SPAN></P><P>&nbsp;</P> Wed, 08 May 2024 17:20:41 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/COUNTAX-DAX-Formel-in-Power-BI-Desktop-need-explanation/m-p/3902993#M152085 Vlad_Akka 2024-05-08T17:20:41Z Re: COUNTAX DAX Formel in Power BI Desktop need explanation https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/COUNTAX-DAX-Formel-in-Power-BI-Desktop-need-explanation/m-p/3903715#M152104 <P>Hi&nbsp;&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="739300" data-lia-user-login="Vlad_Akka" class="lia-mention lia-mention-user">Vlad_Akka</a>&nbsp;,</P> <P>&nbsp;</P> <P>Measure and calculated columns have different row contexts, and the value of the calculated column is calculated based on the table context and is a fixed value.</P> <P>Refer to:</P> <P><A href="https://community.fabric.microsoft.com/t5/Desktop/Using-a-Measure-in-a-Calculated-Column/m-p/425985" target="_blank">Solved: Using a Measure in a Calculated Column - Microsoft Fabric Community</A></P> <P>&nbsp;</P> <P>You may consider the following formula for calculating column dax:</P> <LI-CODE lang="markup">Column = COUNTX( FILTER(ALL('Person List Lookup'), 'Person List Lookup'[CS Course number]=EARLIER('Cousrse Groups Lookup'[CS Course number])),'Person List Lookup'[Personal Nr])</LI-CODE> <P><img /></P> <P>This is the related document, you can view this content:</P> <P><A href="https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/gg413423(v=sql.110)?redirectedfrom=MSDN" target="_blank">Context in DAX Formulas | Microsoft Learn</A></P> <P><A href="https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/" target="_blank">Calculated Columns and Measures in DAX - SQLBI</A></P> <P><A href="https://learn.microsoft.com/en-us/dax/best-practices/dax-column-measure-references" target="_blank">Column and measure references in DAX - DAX | Microsoft Learn</A></P> <P>&nbsp;</P> <P>Best Regards,</P> <P>Liu Yang</P> <P>If this post <STRONG>helps</STRONG>, then please consider <EM>Accept it as the solution</EM> to help the other members find it more quickly.</P> Thu, 09 May 2024 01:40:21 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/COUNTAX-DAX-Formel-in-Power-BI-Desktop-need-explanation/m-p/3903715#M152104 Anonymous 2024-05-09T01:40:21Z Re: COUNTAX DAX Formel in Power BI Desktop need explanation https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/COUNTAX-DAX-Formel-in-Power-BI-Desktop-need-explanation/m-p/3916435#M152458 <P>Thank you for your time and for the help. It was helpful, but i am still wondering why the DAX formula in my question works</P> Tue, 14 May 2024 08:10:14 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/COUNTAX-DAX-Formel-in-Power-BI-Desktop-need-explanation/m-p/3916435#M152458 Vlad_Akka 2024-05-14T08:10:14Z