https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4138357#M164392 <P>Please define what you mean by "Repetition" - do you mean overage in consecutive years?&nbsp; If yes then please provide better sample data with gaps. If no then you can use the measure below.</P> <P>&nbsp;</P> <P><img /></P> <P>&nbsp;</P> <P>&nbsp;</P> Fri, 06 Sep 2024 20:35:55 GMT lbendlin 2024-09-06T20:35:55Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4137991#M164373 <P>Hello all,</P><P>&nbsp;</P><P>I have an overtime data table that has 3 columns in this context:</P><P>Date (01.[Month].[Year])</P><P>EmployeeID</P><P>Overtime Hours</P><P>&nbsp;</P><P>I want to visualize employees that has more than 270 hours of overtime a year consecutively. The reason I want to do this in DAX is because my overtime hours column is a calculated column. It is connected to a parameter that changes the calculation of total overtime hours column based on a rule.&nbsp;</P><P>&nbsp;</P><P>At the end, I provided an input and desired output example. Also I provided more info as I am new to these kind of 'complicated' logics and more info means I can get better insight <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span>&nbsp;</P><P>&nbsp;</P><P>Thank you all,</P><P>&nbsp;</P><P><STRONG>More info:</STRONG></P><P>I have a working temporary solution in power query for now. There are 3 different calculation options. Instead of connecting these to a parameter, I referenced my main data to 3 different queries. Filtered types of overtime based on 3 different calculation options. Grouped by year, pivoted year column and then wrote a very big nested if clause that calculates the repetition amount. I have 3 bookmarks connected to 3 buttons that works like a slicer and switches between 3 identical visuals that are connected to their respective queries.</P><P>&nbsp;</P><P>I am quite new to this kind of logics in PBI. I disliked my current solution as I had to hard code column names and there is a BIG nested if clause. Refresh time went from 5 minutes to 45 minutes.</P><P>&nbsp;</P><P>&nbsp;</P><P><STRONG>Desired Output:</STRONG></P><P>&nbsp;</P><TABLE><TBODY><TR><TD>EmployeeID</TD><TD>Repetition Count</TD></TR><TR><TD>1001</TD><TD>2</TD></TR><TR><TD>1002</TD><TD>0</TD></TR><TR><TD>1003</TD><TD>2</TD></TR><TR><TD>1004</TD><TD>3</TD></TR></TBODY></TABLE><P>&nbsp;</P><P><STRONG>Example Input for above output:</STRONG></P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>EmployeeID</TD><TD>Overtime Hours</TD></TR><TR><TD>1.01.2022</TD><TD>1001</TD><TD>20</TD></TR><TR><TD>1.02.2022</TD><TD>1001</TD><TD>19</TD></TR><TR><TD>1.03.2022</TD><TD>1001</TD><TD>21</TD></TR><TR><TD>1.04.2022</TD><TD>1001</TD><TD>17</TD></TR><TR><TD>1.05.2022</TD><TD>1001</TD><TD>26</TD></TR><TR><TD>1.06.2022</TD><TD>1001</TD><TD>27</TD></TR><TR><TD>1.07.2022</TD><TD>1001</TD><TD>19</TD></TR><TR><TD>1.08.2022</TD><TD>1001</TD><TD>23</TD></TR><TR><TD>1.09.2022</TD><TD>1001</TD><TD>24</TD></TR><TR><TD>1.10.2022</TD><TD>1001</TD><TD>28</TD></TR><TR><TD>1.11.2022</TD><TD>1001</TD><TD>17</TD></TR><TR><TD>1.12.2022</TD><TD>1001</TD><TD>23</TD></TR><TR><TD>1.01.2023</TD><TD>1001</TD><TD>27</TD></TR><TR><TD>1.02.2023</TD><TD>1001</TD><TD>24</TD></TR><TR><TD>1.03.2023</TD><TD>1001</TD><TD>25</TD></TR><TR><TD>1.04.2023</TD><TD>1001</TD><TD>26</TD></TR><TR><TD>1.05.2023</TD><TD>1001</TD><TD>25</TD></TR><TR><TD>1.06.2023</TD><TD>1001</TD><TD>26</TD></TR><TR><TD>1.07.2023</TD><TD>1001</TD><TD>26</TD></TR><TR><TD>1.08.2023</TD><TD>1001</TD><TD>22</TD></TR><TR><TD>1.09.2023</TD><TD>1001</TD><TD>17</TD></TR><TR><TD>1.10.2023</TD><TD>1001</TD><TD>24</TD></TR><TR><TD>1.11.2023</TD><TD>1001</TD><TD>24</TD></TR><TR><TD>1.12.2023</TD><TD>1001</TD><TD>18</TD></TR><TR><TD>1.01.2024</TD><TD>1001</TD><TD>34</TD></TR><TR><TD>1.02.2024</TD><TD>1001</TD><TD>34</TD></TR><TR><TD>1.03.2024</TD><TD>1001</TD><TD>35</TD></TR><TR><TD>1.04.2024</TD><TD>1001</TD><TD>38</TD></TR><TR><TD>1.05.2024</TD><TD>1001</TD><TD>41</TD></TR><TR><TD>1.06.2024</TD><TD>1001</TD><TD>27</TD></TR><TR><TD>1.07.2024</TD><TD>1001</TD><TD>35</TD></TR><TR><TD>1.08.2024</TD><TD>1001</TD><TD>36</TD></TR><TR><TD>1.01.2022</TD><TD>1002</TD><TD>26</TD></TR><TR><TD>1.02.2022</TD><TD>1002</TD><TD>21</TD></TR><TR><TD>1.03.2022</TD><TD>1002</TD><TD>25</TD></TR><TR><TD>1.04.2022</TD><TD>1002</TD><TD>17</TD></TR><TR><TD>1.05.2022</TD><TD>1002</TD><TD>21</TD></TR><TR><TD>1.06.2022</TD><TD>1002</TD><TD>28</TD></TR><TR><TD>1.07.2022</TD><TD>1002</TD><TD>22</TD></TR><TR><TD>1.08.2022</TD><TD>1002</TD><TD>18</TD></TR><TR><TD>1.09.2022</TD><TD>1002</TD><TD>22</TD></TR><TR><TD>1.10.2022</TD><TD>1002</TD><TD>18</TD></TR><TR><TD>1.11.2022</TD><TD>1002</TD><TD>18</TD></TR><TR><TD>1.12.2022</TD><TD>1002</TD><TD>28</TD></TR><TR><TD>1.01.2023</TD><TD>1002</TD><TD>20</TD></TR><TR><TD>1.02.2023</TD><TD>1002</TD><TD>22</TD></TR><TR><TD>1.03.2023</TD><TD>1002</TD><TD>23</TD></TR><TR><TD>1.04.2023</TD><TD>1002</TD><TD>21</TD></TR><TR><TD>1.05.2023</TD><TD>1002</TD><TD>17</TD></TR><TR><TD>1.06.2023</TD><TD>1002</TD><TD>20</TD></TR><TR><TD>1.07.2023</TD><TD>1002</TD><TD>19</TD></TR><TR><TD>1.08.2023</TD><TD>1002</TD><TD>28</TD></TR><TR><TD>1.09.2023</TD><TD>1002</TD><TD>19</TD></TR><TR><TD>1.10.2023</TD><TD>1002</TD><TD>27</TD></TR><TR><TD>1.11.2023</TD><TD>1002</TD><TD>20</TD></TR><TR><TD>1.12.2023</TD><TD>1002</TD><TD>19</TD></TR><TR><TD>1.01.2024</TD><TD>1002</TD><TD>27</TD></TR><TR><TD>1.02.2024</TD><TD>1002</TD><TD>37</TD></TR><TR><TD>1.03.2024</TD><TD>1002</TD><TD>27</TD></TR><TR><TD>1.04.2024</TD><TD>1002</TD><TD>26</TD></TR><TR><TD>1.05.2024</TD><TD>1002</TD><TD>36</TD></TR><TR><TD>1.06.2024</TD><TD>1002</TD><TD>27</TD></TR><TR><TD>1.07.2024</TD><TD>1002</TD><TD>34</TD></TR><TR><TD>1.08.2024</TD><TD>1002</TD><TD>31</TD></TR><TR><TD>1.01.2022</TD><TD>1003</TD><TD>25</TD></TR><TR><TD>1.02.2022</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.03.2022</TD><TD>1003</TD><TD>23</TD></TR><TR><TD>1.04.2022</TD><TD>1003</TD><TD>23</TD></TR><TR><TD>1.05.2022</TD><TD>1003</TD><TD>20</TD></TR><TR><TD>1.06.2022</TD><TD>1003</TD><TD>17</TD></TR><TR><TD>1.07.2022</TD><TD>1003</TD><TD>25</TD></TR><TR><TD>1.08.2022</TD><TD>1003</TD><TD>24</TD></TR><TR><TD>1.09.2022</TD><TD>1003</TD><TD>22</TD></TR><TR><TD>1.10.2022</TD><TD>1003</TD><TD>18</TD></TR><TR><TD>1.11.2022</TD><TD>1003</TD><TD>26</TD></TR><TR><TD>1.12.2022</TD><TD>1003</TD><TD>26</TD></TR><TR><TD>1.01.2023</TD><TD>1003</TD><TD>25</TD></TR><TR><TD>1.02.2023</TD><TD>1003</TD><TD>21</TD></TR><TR><TD>1.03.2023</TD><TD>1003</TD><TD>24</TD></TR><TR><TD>1.04.2023</TD><TD>1003</TD><TD>27</TD></TR><TR><TD>1.05.2023</TD><TD>1003</TD><TD>27</TD></TR><TR><TD>1.06.2023</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.07.2023</TD><TD>1003</TD><TD>23</TD></TR><TR><TD>1.08.2023</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.09.2023</TD><TD>1003</TD><TD>20</TD></TR><TR><TD>1.10.2023</TD><TD>1003</TD><TD>26</TD></TR><TR><TD>1.11.2023</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.12.2023</TD><TD>1003</TD><TD>27</TD></TR><TR><TD>1.01.2024</TD><TD>1003</TD><TD>42</TD></TR><TR><TD>1.02.2024</TD><TD>1003</TD><TD>42</TD></TR><TR><TD>1.03.2024</TD><TD>1003</TD><TD>40</TD></TR><TR><TD>1.04.2024</TD><TD>1003</TD><TD>27</TD></TR><TR><TD>1.05.2024</TD><TD>1003</TD><TD>30</TD></TR><TR><TD>1.06.2024</TD><TD>1003</TD><TD>30</TD></TR><TR><TD>1.07.2024</TD><TD>1003</TD><TD>34</TD></TR><TR><TD>1.08.2024</TD><TD>1003</TD><TD>41</TD></TR><TR><TD>1.01.2022</TD><TD>1004</TD><TD>28</TD></TR><TR><TD>1.02.2022</TD><TD>1004</TD><TD>18</TD></TR><TR><TD>1.03.2022</TD><TD>1004</TD><TD>28</TD></TR><TR><TD>1.04.2022</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.05.2022</TD><TD>1004</TD><TD>28</TD></TR><TR><TD>1.06.2022</TD><TD>1004</TD><TD>22</TD></TR><TR><TD>1.07.2022</TD><TD>1004</TD><TD>18</TD></TR><TR><TD>1.08.2022</TD><TD>1004</TD><TD>22</TD></TR><TR><TD>1.09.2022</TD><TD>1004</TD><TD>24</TD></TR><TR><TD>1.10.2022</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.11.2022</TD><TD>1004</TD><TD>22</TD></TR><TR><TD>1.12.2022</TD><TD>1004</TD><TD>18</TD></TR><TR><TD>1.01.2023</TD><TD>1004</TD><TD>20</TD></TR><TR><TD>1.02.2023</TD><TD>1004</TD><TD>19</TD></TR><TR><TD>1.03.2023</TD><TD>1004</TD><TD>20</TD></TR><TR><TD>1.04.2023</TD><TD>1004</TD><TD>21</TD></TR><TR><TD>1.05.2023</TD><TD>1004</TD><TD>26</TD></TR><TR><TD>1.06.2023</TD><TD>1004</TD><TD>24</TD></TR><TR><TD>1.07.2023</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.08.2023</TD><TD>1004</TD><TD>24</TD></TR><TR><TD>1.09.2023</TD><TD>1004</TD><TD>22</TD></TR><TR><TD>1.10.2023</TD><TD>1004</TD><TD>26</TD></TR><TR><TD>1.11.2023</TD><TD>1004</TD><TD>19</TD></TR><TR><TD>1.12.2023</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.01.2024</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.02.2024</TD><TD>1004</TD><TD>34</TD></TR><TR><TD>1.03.2024</TD><TD>1004</TD><TD>39</TD></TR><TR><TD>1.04.2024</TD><TD>1004</TD><TD>34</TD></TR><TR><TD>1.05.2024</TD><TD>1004</TD><TD>37</TD></TR><TR><TD>1.06.2024</TD><TD>1004</TD><TD>37</TD></TR><TR><TD>1.07.2024</TD><TD>1004</TD><TD>39</TD></TR><TR><TD>1.08.2024</TD><TD>1004</TD><TD>37</TD></TR></TBODY></TABLE> Fri, 06 Sep 2024 15:49:18 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4137991#M164373 Anonymous 2024-09-06T15:49:18Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4138357#M164392 <P>Please define what you mean by "Repetition" - do you mean overage in consecutive years?&nbsp; If yes then please provide better sample data with gaps. If no then you can use the measure below.</P> <P>&nbsp;</P> <P><img /></P> <P>&nbsp;</P> <P>&nbsp;</P> Fri, 06 Sep 2024 20:35:55 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4138357#M164392 lbendlin 2024-09-06T20:35:55Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4139328#M164437 <P>Hello lbendlin,</P><P>&nbsp;</P><P>Thank you for pointing that one out. Yes, say, if someone did 270+ from 2020 to 2023 but then they were 270- in 2024, Count value I am looking for is 0.</P><P>&nbsp;</P><P>Here is a better example output and input:</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>1001</TD><TD>2</TD></TR><TR><TD>1002</TD><TD>1</TD></TR><TR><TD>1003</TD><TD>2</TD></TR><TR><TD>1004</TD><TD>4</TD></TR></TBODY></TABLE><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>EmployeeID</TD><TD>Overtime Hours</TD></TR><TR><TD>1.01.2022</TD><TD>1001</TD><TD>20</TD></TR><TR><TD>1.02.2022</TD><TD>1001</TD><TD>19</TD></TR><TR><TD>1.03.2022</TD><TD>1001</TD><TD>21</TD></TR><TR><TD>1.04.2022</TD><TD>1001</TD><TD>17</TD></TR><TR><TD>1.05.2022</TD><TD>1001</TD><TD>26</TD></TR><TR><TD>1.06.2022</TD><TD>1001</TD><TD>27</TD></TR><TR><TD>1.07.2022</TD><TD>1001</TD><TD>19</TD></TR><TR><TD>1.08.2022</TD><TD>1001</TD><TD>23</TD></TR><TR><TD>1.09.2022</TD><TD>1001</TD><TD>24</TD></TR><TR><TD>1.10.2022</TD><TD>1001</TD><TD>28</TD></TR><TR><TD>1.11.2022</TD><TD>1001</TD><TD>17</TD></TR><TR><TD>1.12.2022</TD><TD>1001</TD><TD>23</TD></TR><TR><TD>1.01.2023</TD><TD>1001</TD><TD>27</TD></TR><TR><TD>1.02.2023</TD><TD>1001</TD><TD>24</TD></TR><TR><TD>1.03.2023</TD><TD>1001</TD><TD>25</TD></TR><TR><TD>1.04.2023</TD><TD>1001</TD><TD>26</TD></TR><TR><TD>1.05.2023</TD><TD>1001</TD><TD>25</TD></TR><TR><TD>1.06.2023</TD><TD>1001</TD><TD>26</TD></TR><TR><TD>1.07.2023</TD><TD>1001</TD><TD>26</TD></TR><TR><TD>1.08.2023</TD><TD>1001</TD><TD>22</TD></TR><TR><TD>1.09.2023</TD><TD>1001</TD><TD>17</TD></TR><TR><TD>1.10.2023</TD><TD>1001</TD><TD>24</TD></TR><TR><TD>1.11.2023</TD><TD>1001</TD><TD>24</TD></TR><TR><TD>1.12.2023</TD><TD>1001</TD><TD>18</TD></TR><TR><TD>1.01.2024</TD><TD>1001</TD><TD>34</TD></TR><TR><TD>1.02.2024</TD><TD>1001</TD><TD>34</TD></TR><TR><TD>1.03.2024</TD><TD>1001</TD><TD>35</TD></TR><TR><TD>1.04.2024</TD><TD>1001</TD><TD>38</TD></TR><TR><TD>1.05.2024</TD><TD>1001</TD><TD>41</TD></TR><TR><TD>1.06.2024</TD><TD>1001</TD><TD>27</TD></TR><TR><TD>1.07.2024</TD><TD>1001</TD><TD>35</TD></TR><TR><TD>1.08.2024</TD><TD>1001</TD><TD>36</TD></TR><TR><TD>1.01.2022</TD><TD>1002</TD><TD>26</TD></TR><TR><TD>1.02.2022</TD><TD>1002</TD><TD>21</TD></TR><TR><TD>1.03.2022</TD><TD>1002</TD><TD>25</TD></TR><TR><TD>1.04.2022</TD><TD>1002</TD><TD>17</TD></TR><TR><TD>1.05.2022</TD><TD>1002</TD><TD>21</TD></TR><TR><TD>1.06.2022</TD><TD>1002</TD><TD>28</TD></TR><TR><TD>1.07.2022</TD><TD>1002</TD><TD>22</TD></TR><TR><TD>1.08.2022</TD><TD>1002</TD><TD>18</TD></TR><TR><TD>1.09.2022</TD><TD>1002</TD><TD>22</TD></TR><TR><TD>1.10.2022</TD><TD>1002</TD><TD>18</TD></TR><TR><TD>1.11.2022</TD><TD>1002</TD><TD>18</TD></TR><TR><TD>1.12.2022</TD><TD>1002</TD><TD>28</TD></TR><TR><TD>1.01.2023</TD><TD>1002</TD><TD>20</TD></TR><TR><TD>1.02.2023</TD><TD>1002</TD><TD>22</TD></TR><TR><TD>1.03.2023</TD><TD>1002</TD><TD>23</TD></TR><TR><TD>1.04.2023</TD><TD>1002</TD><TD>21</TD></TR><TR><TD>1.05.2023</TD><TD>1002</TD><TD>17</TD></TR><TR><TD>1.06.2023</TD><TD>1002</TD><TD>20</TD></TR><TR><TD>1.07.2023</TD><TD>1002</TD><TD>19</TD></TR><TR><TD>1.08.2023</TD><TD>1002</TD><TD>28</TD></TR><TR><TD>1.09.2023</TD><TD>1002</TD><TD>19</TD></TR><TR><TD>1.10.2023</TD><TD>1002</TD><TD>27</TD></TR><TR><TD>1.11.2023</TD><TD>1002</TD><TD>20</TD></TR><TR><TD>1.12.2023</TD><TD>1002</TD><TD>19</TD></TR><TR><TD>1.01.2024</TD><TD>1002</TD><TD>35</TD></TR><TR><TD>1.02.2024</TD><TD>1002</TD><TD>37</TD></TR><TR><TD>1.03.2024</TD><TD>1002</TD><TD>35</TD></TR><TR><TD>1.04.2024</TD><TD>1002</TD><TD>26</TD></TR><TR><TD>1.05.2024</TD><TD>1002</TD><TD>36</TD></TR><TR><TD>1.06.2024</TD><TD>1002</TD><TD>35</TD></TR><TR><TD>1.07.2024</TD><TD>1002</TD><TD>34</TD></TR><TR><TD>1.08.2024</TD><TD>1002</TD><TD>34</TD></TR><TR><TD>1.01.2022</TD><TD>1003</TD><TD>25</TD></TR><TR><TD>1.02.2022</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.03.2022</TD><TD>1003</TD><TD>23</TD></TR><TR><TD>1.04.2022</TD><TD>1003</TD><TD>23</TD></TR><TR><TD>1.05.2022</TD><TD>1003</TD><TD>20</TD></TR><TR><TD>1.06.2022</TD><TD>1003</TD><TD>17</TD></TR><TR><TD>1.07.2022</TD><TD>1003</TD><TD>25</TD></TR><TR><TD>1.08.2022</TD><TD>1003</TD><TD>24</TD></TR><TR><TD>1.09.2022</TD><TD>1003</TD><TD>22</TD></TR><TR><TD>1.10.2022</TD><TD>1003</TD><TD>18</TD></TR><TR><TD>1.11.2022</TD><TD>1003</TD><TD>26</TD></TR><TR><TD>1.12.2022</TD><TD>1003</TD><TD>26</TD></TR><TR><TD>1.01.2023</TD><TD>1003</TD><TD>25</TD></TR><TR><TD>1.02.2023</TD><TD>1003</TD><TD>21</TD></TR><TR><TD>1.03.2023</TD><TD>1003</TD><TD>24</TD></TR><TR><TD>1.04.2023</TD><TD>1003</TD><TD>27</TD></TR><TR><TD>1.05.2023</TD><TD>1003</TD><TD>27</TD></TR><TR><TD>1.06.2023</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.07.2023</TD><TD>1003</TD><TD>23</TD></TR><TR><TD>1.08.2023</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.09.2023</TD><TD>1003</TD><TD>20</TD></TR><TR><TD>1.10.2023</TD><TD>1003</TD><TD>26</TD></TR><TR><TD>1.11.2023</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.12.2023</TD><TD>1003</TD><TD>27</TD></TR><TR><TD>1.01.2024</TD><TD>1003</TD><TD>42</TD></TR><TR><TD>1.02.2024</TD><TD>1003</TD><TD>42</TD></TR><TR><TD>1.03.2024</TD><TD>1003</TD><TD>40</TD></TR><TR><TD>1.04.2024</TD><TD>1003</TD><TD>27</TD></TR><TR><TD>1.05.2024</TD><TD>1003</TD><TD>30</TD></TR><TR><TD>1.06.2024</TD><TD>1003</TD><TD>30</TD></TR><TR><TD>1.07.2024</TD><TD>1003</TD><TD>34</TD></TR><TR><TD>1.08.2024</TD><TD>1003</TD><TD>41</TD></TR><TR><TD>1.01.2022</TD><TD>1004</TD><TD>28</TD></TR><TR><TD>1.02.2022</TD><TD>1004</TD><TD>18</TD></TR><TR><TD>1.03.2022</TD><TD>1004</TD><TD>28</TD></TR><TR><TD>1.04.2022</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.05.2022</TD><TD>1004</TD><TD>28</TD></TR><TR><TD>1.06.2022</TD><TD>1004</TD><TD>22</TD></TR><TR><TD>1.07.2022</TD><TD>1004</TD><TD>18</TD></TR><TR><TD>1.08.2022</TD><TD>1004</TD><TD>22</TD></TR><TR><TD>1.09.2022</TD><TD>1004</TD><TD>24</TD></TR><TR><TD>1.10.2022</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.11.2022</TD><TD>1004</TD><TD>22</TD></TR><TR><TD>1.12.2022</TD><TD>1004</TD><TD>18</TD></TR><TR><TD>1.01.2023</TD><TD>1004</TD><TD>20</TD></TR><TR><TD>1.02.2023</TD><TD>1004</TD><TD>19</TD></TR><TR><TD>1.03.2023</TD><TD>1004</TD><TD>20</TD></TR><TR><TD>1.04.2023</TD><TD>1004</TD><TD>21</TD></TR><TR><TD>1.05.2023</TD><TD>1004</TD><TD>26</TD></TR><TR><TD>1.06.2023</TD><TD>1004</TD><TD>24</TD></TR><TR><TD>1.07.2023</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.08.2023</TD><TD>1004</TD><TD>24</TD></TR><TR><TD>1.09.2023</TD><TD>1004</TD><TD>22</TD></TR><TR><TD>1.10.2023</TD><TD>1004</TD><TD>26</TD></TR><TR><TD>1.11.2023</TD><TD>1004</TD><TD>19</TD></TR><TR><TD>1.12.2023</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.01.2024</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.02.2024</TD><TD>1004</TD><TD>34</TD></TR><TR><TD>1.03.2024</TD><TD>1004</TD><TD>39</TD></TR><TR><TD>1.04.2024</TD><TD>1004</TD><TD>34</TD></TR><TR><TD>1.05.2024</TD><TD>1004</TD><TD>37</TD></TR><TR><TD>1.06.2024</TD><TD>1004</TD><TD>37</TD></TR><TR><TD>1.07.2024</TD><TD>1004</TD><TD>39</TD></TR><TR><TD>1.08.2024</TD><TD>1004</TD><TD>37</TD></TR><TR><TD>1.01.2021</TD><TD>1001</TD><TD>20</TD></TR><TR><TD>1.02.2021</TD><TD>1001</TD><TD>19</TD></TR><TR><TD>1.03.2021</TD><TD>1001</TD><TD>21</TD></TR><TR><TD>1.04.2021</TD><TD>1001</TD><TD>21</TD></TR><TR><TD>1.05.2021</TD><TD>1001</TD><TD>26</TD></TR><TR><TD>1.06.2021</TD><TD>1001</TD><TD>27</TD></TR><TR><TD>1.07.2021</TD><TD>1001</TD><TD>23</TD></TR><TR><TD>1.08.2021</TD><TD>1001</TD><TD>23</TD></TR><TR><TD>1.09.2021</TD><TD>1001</TD><TD>24</TD></TR><TR><TD>1.10.2021</TD><TD>1001</TD><TD>28</TD></TR><TR><TD>1.11.2021</TD><TD>1001</TD><TD>17</TD></TR><TR><TD>1.12.2021</TD><TD>1001</TD><TD>23</TD></TR><TR><TD>1.01.2021</TD><TD>1002</TD><TD>27</TD></TR><TR><TD>1.02.2021</TD><TD>1002</TD><TD>24</TD></TR><TR><TD>1.03.2021</TD><TD>1002</TD><TD>25</TD></TR><TR><TD>1.04.2021</TD><TD>1002</TD><TD>26</TD></TR><TR><TD>1.05.2021</TD><TD>1002</TD><TD>25</TD></TR><TR><TD>1.06.2021</TD><TD>1002</TD><TD>26</TD></TR><TR><TD>1.07.2021</TD><TD>1002</TD><TD>26</TD></TR><TR><TD>1.08.2021</TD><TD>1002</TD><TD>22</TD></TR><TR><TD>1.09.2021</TD><TD>1002</TD><TD>17</TD></TR><TR><TD>1.10.2021</TD><TD>1002</TD><TD>24</TD></TR><TR><TD>1.11.2021</TD><TD>1002</TD><TD>24</TD></TR><TR><TD>1.12.2021</TD><TD>1002</TD><TD>18</TD></TR><TR><TD>1.01.2021</TD><TD>1003</TD><TD>20</TD></TR><TR><TD>1.02.2021</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.03.2021</TD><TD>1003</TD><TD>21</TD></TR><TR><TD>1.04.2021</TD><TD>1003</TD><TD>17</TD></TR><TR><TD>1.05.2021</TD><TD>1003</TD><TD>26</TD></TR><TR><TD>1.06.2021</TD><TD>1003</TD><TD>27</TD></TR><TR><TD>1.07.2021</TD><TD>1003</TD><TD>19</TD></TR><TR><TD>1.08.2021</TD><TD>1003</TD><TD>23</TD></TR><TR><TD>1.09.2021</TD><TD>1003</TD><TD>24</TD></TR><TR><TD>1.10.2021</TD><TD>1003</TD><TD>28</TD></TR><TR><TD>1.11.2021</TD><TD>1003</TD><TD>17</TD></TR><TR><TD>1.12.2021</TD><TD>1003</TD><TD>23</TD></TR><TR><TD>1.01.2021</TD><TD>1004</TD><TD>27</TD></TR><TR><TD>1.02.2021</TD><TD>1004</TD><TD>24</TD></TR><TR><TD>1.03.2021</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.04.2021</TD><TD>1004</TD><TD>26</TD></TR><TR><TD>1.05.2021</TD><TD>1004</TD><TD>25</TD></TR><TR><TD>1.06.2021</TD><TD>1004</TD><TD>26</TD></TR><TR><TD>1.07.2021</TD><TD>1004</TD><TD>26</TD></TR><TR><TD>1.08.2021</TD><TD>1004</TD><TD>22</TD></TR><TR><TD>1.09.2021</TD><TD>1004</TD><TD>17</TD></TR><TR><TD>1.10.2021</TD><TD>1004</TD><TD>24</TD></TR><TR><TD>1.11.2021</TD><TD>1004</TD><TD>24</TD></TR><TR><TD>1.12.2021</TD><TD>1004</TD><TD>18</TD></TR></TBODY></TABLE> Sun, 08 Sep 2024 15:32:33 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4139328#M164437 Anonymous 2024-09-08T15:32:33Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4144345#M164705 <P>Hi&nbsp;Anonymous</LI-USER>&nbsp;,</P> <P>I create a table and two columns as you mentioned.</P> <LI-CODE lang="markup">Year = YEAR('Table'[Date])</LI-CODE> <P><img /></P> <LI-CODE lang="markup">Column = IF('Table'[YearlyOvertimeHours]&gt;270,"+","-")</LI-CODE> <P><img /></P> <P>Then I calculate the total result and make a new table.</P> <LI-CODE lang="markup">YearlyOvertimeHours = CALCULATE( SUM('Table'[Overtime Hours]), ALLEXCEPT('Table', 'Table'[EmployeeID], 'Table'[Year]) )</LI-CODE><LI-CODE lang="markup">Table 2 = SUMMARIZE ( SELECTCOLUMNS ( 'Table', "Year", 'Table'[Year], "EmployeeID", 'Table'[EmployeeID], "YearlyOvertimeHours", 'Table'[YearlyOvertimeHours], "Column", 'Table'[Column] ), [Year], [EmployeeID], [YearlyOvertimeHours], [Column] )</LI-CODE> <P><img /></P> <P>Next I create another two calculated columns.</P> <LI-CODE lang="markup">Column 2 = RANKX ( FILTER ( ALLSELECTED ( 'Table 2' ), 'Table 2'[EmployeeID] = EARLIER ( 'Table 2'[EmployeeID] ) &amp;&amp; 'Table 2'[Column] = "-" ), [Year], , ASC )</LI-CODE><LI-CODE lang="markup">Column 3 = VAR _vtable = ADDCOLUMNS ( ALLSELECTED ( 'Table 2' ), "_Count", COUNTROWS ( FILTER ( ALLSELECTED ( 'Table 2' ), 'Table 2'[EmployeeID] = EARLIER ( 'Table 2'[EmployeeID] ) &amp;&amp; 'Table 2'[Column 2] = EARLIER ( 'Table 2'[Column 2] ) &amp;&amp; 'Table 2'[Column] = "+" ) ) - 1 ) VAR _vtable2 = SELECTCOLUMNS ( FILTER ( SUMMARIZE ( _vtable, 'Table 2'[EmployeeID], 'Table 2'[Column], "_MaxYear", MAX ( 'Table 2'[Year] ), "_maxx", MAXX ( FILTER ( _vtable, 'Table 2'[EmployeeID] = EARLIER ( 'Table 2'[EmployeeID] ) ), [_Count] ) ), 'Table 2'[Column] = "+" ), "_ID", 'Table 2'[EmployeeID], "_outcome", [_maxx] ) RETURN COUNTROWS ( FILTER ( SELECTCOLUMNS ( FILTER ( SUMMARIZE ( 'Table 2', 'Table 2'[EmployeeID], 'Table 2'[Column], "_MaxYear", MAX ( 'Table 2'[Year] ) ), 'Table 2'[Column] = "+" ), "_ID", [EmployeeID], "_Year", [_MaxYear] ), [_ID] = 'Table 2'[EmployeeID] ) ) + MAXX ( FILTER ( _vtable2, [_ID] = 'Table 2'[EmployeeID] ), [_outcome] ) </LI-CODE> <P>Finally you can get what you want.</P> <P><img /></P> <P>&nbsp;</P> <P>&nbsp;</P> <P>&nbsp;</P> <P><SPAN>Best Regards</SPAN></P> <P><SPAN>Yilong Zhou</SPAN></P> <P><SPAN>If this post&nbsp;<STRONG><I>helps</I></STRONG>, then please consider&nbsp;<STRONG><I>Accept it as the solution</I></STRONG>&nbsp;to help the other members find it more quickly.</SPAN></P> Wed, 11 Sep 2024 07:47:21 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4144345#M164705 Anonymous 2024-09-11T07:47:21Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4147154#M164797 <P>this did it. thank you so much <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Thu, 12 Sep 2024 12:06:47 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Determining-EmployeeIDs-with-value-sums-above-threshold-X-years/m-p/4147154#M164797 Anonymous 2024-09-12T12:06:47Z