topic Re: Conditional format latest value per user in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-format-latest-value-per-user/m-p/4378480#M173832 <P>Your measure and my original measure are basically the same. My measure is already working for assessing the latest week or not. I don't understand why it isn't being accepted as part of the conditional formatting.&nbsp;</P> Thu, 23 Jan 2025 20:55:04 GMT deaconb 2025-01-23T20:55:04Z Conditional format latest value per user https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-format-latest-value-per-user/m-p/4378252#M173825 <P>I'm working on a report which pulls together several sources of training data. One of the widgets we need is for new employees working through the training program and capturing their training events by week. The ask from stakeholders is to color code a given user's lastest training week which I felt I could accomplish from conditional formatting.&nbsp;</P><P>&nbsp;</P><P>I have the following formulas:&nbsp;</P><P>1. Week of Training</P><DIV><PRE><SPAN>Week of Training =</SPAN><BR /><BR /><SPAN>var</SPAN> <SPAN>saturdaybefore</SPAN><SPAN> =&nbsp;</SPAN><BR /><SPAN>&nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><BR /><SPAN>&nbsp; &nbsp; (</SPAN><SPAN>MIN</SPAN><SPAN>(</SPAN><SPAN>'Employee'</SPAN><SPAN>[Proficiency Training Start Date]</SPAN><SPAN>) - </SPAN><SPAN>WEEKDAY</SPAN><SPAN>(</SPAN><SPAN>MIN</SPAN><SPAN>(</SPAN><SPAN>'Employee'</SPAN><SPAN>[Proficiency Training Start Date]</SPAN><SPAN>), </SPAN><SPAN>2</SPAN><SPAN>))-</SPAN><SPAN>1</SPAN><BR /><BR /><SPAN>RETURN</SPAN><BR /><SPAN>IF</SPAN><SPAN>(</SPAN><SPAN>YEAR</SPAN><SPAN>(</SPAN><SPAN>saturdaybefore</SPAN><SPAN>) &lt; </SPAN><SPAN>YEAR</SPAN><SPAN>(</SPAN><SPAN>MIN</SPAN><SPAN>(</SPAN><SPAN>'Employee'</SPAN><SPAN>[Proficiency Training Start Date]</SPAN><SPAN>)), </SPAN><SPAN>BLANK</SPAN><SPAN>(), </SPAN><SPAN>ROUNDUP</SPAN><SPAN>(</SPAN><SPAN>DIVIDE</SPAN><SPAN>(</SPAN><SPAN>DATEDIFF</SPAN><SPAN>(</SPAN><SPAN>saturdaybefore</SPAN><SPAN>, </SPAN><SPAN>'Data'</SPAN><SPAN>[Date of Training]</SPAN><SPAN>, </SPAN><SPAN>Day</SPAN><SPAN>),</SPAN><SPAN>7</SPAN><SPAN>), </SPAN><SPAN>0</SPAN><SPAN>))</SPAN></PRE><P>&nbsp;</P><P>2. I then created a measure to assess the max week of each user:&nbsp;</P><DIV><PRE><SPAN>Measure</SPAN><SPAN> =</SPAN><BR /><BR /><SPAN>var</SPAN> <SPAN>maxweek</SPAN><SPAN> = </SPAN><BR /><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><BR /><SPAN>&nbsp; &nbsp; </SPAN><SPAN>MAXX</SPAN><SPAN>(</SPAN><SPAN>'Data'</SPAN><SPAN>,</SPAN><SPAN>[Week of Training]</SPAN><SPAN> ),</SPAN><BR /><SPAN>&nbsp; &nbsp; </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>(Data</SPAN><SPAN>'</SPAN><SPAN>, </SPAN><SPAN>'Data</SPAN><SPAN>'</SPAN><SPAN>[USERNAME]</SPAN><SPAN>)</SPAN><BR /><SPAN>)</SPAN><BR /><BR /><SPAN>RETURN</SPAN><BR /><SPAN>IF</SPAN><SPAN>(</SPAN><SPAN>ISBLANK</SPAN><SPAN>(</SPAN><SPAN>maxweek</SPAN><SPAN>), </SPAN><SPAN>0</SPAN><SPAN>, <BR /></SPAN><SPAN>IF</SPAN><SPAN>(</SPAN><SPAN>Max</SPAN><SPAN>(</SPAN><SPAN>'Data'</SPAN><SPAN>[Week of Training]</SPAN><SPAN>) =</SPAN><SPAN>maxweek</SPAN><SPAN>, </SPAN><SPAN>2</SPAN><SPAN>, </SPAN><SPAN>1</SPAN><SPAN>)<BR />)<BR /></SPAN></PRE></DIV><DIV>&nbsp;</DIV><DIV>&nbsp;</DIV><DIV><SPAN>I want to color a given user's maximum week orange. I thought I could accompish doing this with my measure above and setting 2 to be orange (the field format is number), but it's only formatting the absolute latest training date (e.g., green box value) and not everyone's latest training week date (e.g. red box values). I realize I am currently using gradient conditional formatting but it shouldn't affect that the latest training week for a user isn't shading properly.</SPAN></DIV><DIV><img /><P>&nbsp;</P></DIV><DIV>&nbsp;</DIV><DIV><SPAN>Here's some truncated sample data to show you my measure is working properly:&nbsp;</SPAN></DIV><DIV><TABLE><TBODY><TR><TD>USERNAME</TD><TD>Date of Training</TD><TD>&nbsp;SYSID</TD><TD>Proficiency Start Date</TD><TD>Week of Training</TD><TD>Measure</TD></TR><TR><TD>user1</TD><TD>11/6/2024 00:00</TD><TD>8609789</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>0</TD></TR><TR><TD>user2</TD><TD>1/21/2025 00:00</TD><TD>9043094</TD><TD>10/7/2024 00:00</TD><TD>16</TD><TD>2</TD></TR><TR><TD>user2</TD><TD>12/15/2024 00:00</TD><TD>8873091</TD><TD>10/7/2024 00:00</TD><TD>11</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>12/11/2024 00:00</TD><TD>8850769</TD><TD>10/7/2024 00:00</TD><TD>10</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>12/11/2024 00:00</TD><TD>8850741</TD><TD>10/7/2024 00:00</TD><TD>10</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>12/2/2024 00:00</TD><TD>8786133</TD><TD>10/7/2024 00:00</TD><TD>9</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>12/1/2024 00:00</TD><TD>8777767</TD><TD>10/7/2024 00:00</TD><TD>9</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>12/1/2024 00:00</TD><TD>8777763</TD><TD>10/7/2024 00:00</TD><TD>9</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>11/30/2024 00:00</TD><TD>8775141</TD><TD>10/7/2024 00:00</TD><TD>8</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>11/30/2024 00:00</TD><TD>8775133</TD><TD>10/7/2024 00:00</TD><TD>8</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>11/27/2024 00:00</TD><TD>8760819</TD><TD>10/7/2024 00:00</TD><TD>8</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>11/17/2024 00:00</TD><TD>8684304</TD><TD>10/7/2024 00:00</TD><TD>7</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>11/13/2024 00:00</TD><TD>8659422</TD><TD>10/7/2024 00:00</TD><TD>6</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>11/4/2024 00:00</TD><TD>8592577</TD><TD>10/7/2024 00:00</TD><TD>5</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>11/3/2024 00:00</TD><TD>8584769</TD><TD>10/7/2024 00:00</TD><TD>5</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/30/2024 00:00</TD><TD>8565134</TD><TD>10/7/2024 00:00</TD><TD>4</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/30/2024 00:00</TD><TD>8565131</TD><TD>10/7/2024 00:00</TD><TD>4</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/30/2024 00:00</TD><TD>8565127</TD><TD>10/7/2024 00:00</TD><TD>4</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/24/2024 00:00</TD><TD>8526851</TD><TD>10/7/2024 00:00</TD><TD>3</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/22/2024 00:00</TD><TD>8505041</TD><TD>10/7/2024 00:00</TD><TD>3</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/22/2024 00:00</TD><TD>8505026</TD><TD>10/7/2024 00:00</TD><TD>3</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/16/2024 00:00</TD><TD>8467188</TD><TD>10/7/2024 00:00</TD><TD>2</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/16/2024 00:00</TD><TD>8467188</TD><TD>10/7/2024 00:00</TD><TD>2</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/16/2024 00:00</TD><TD>8467188</TD><TD>10/7/2024 00:00</TD><TD>2</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/11/2024 00:00</TD><TD>8439690</TD><TD>10/7/2024 00:00</TD><TD>1</TD><TD>1</TD></TR><TR><TD>user2</TD><TD>10/11/2024 00:00</TD><TD>8439690</TD><TD>10/7/2024 00:00</TD><TD>1</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>12/14/2024 00:00</TD><TD>8870221</TD><TD>10/7/2024 00:00</TD><TD>10</TD><TD>2</TD></TR><TR><TD>user3</TD><TD>12/8/2024 00:00</TD><TD>8827785</TD><TD>10/7/2024 00:00</TD><TD>10</TD><TD>2</TD></TR><TR><TD>user3</TD><TD>12/5/2024 00:00</TD><TD>8807877</TD><TD>10/7/2024 00:00</TD><TD>9</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>12/4/2024 00:00</TD><TD>8807664</TD><TD>10/7/2024 00:00</TD><TD>9</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>12/4/2024 00:00</TD><TD>8806990</TD><TD>10/7/2024 00:00</TD><TD>9</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/26/2024 00:00</TD><TD>8744739</TD><TD>10/7/2024 00:00</TD><TD>8</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/25/2024 00:00</TD><TD>8737160</TD><TD>10/7/2024 00:00</TD><TD>8</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/24/2024 00:00</TD><TD>8732020</TD><TD>10/7/2024 00:00</TD><TD>8</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/23/2024 00:00</TD><TD>8731822</TD><TD>10/7/2024 00:00</TD><TD>7</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/20/2024 00:00</TD><TD>8710436</TD><TD>10/7/2024 00:00</TD><TD>7</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/10/2024 00:00</TD><TD>8633342</TD><TD>10/7/2024 00:00</TD><TD>6</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/10/2024 00:00</TD><TD>8633326</TD><TD>10/7/2024 00:00</TD><TD>6</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/9/2024 00:00</TD><TD>8630349</TD><TD>10/7/2024 00:00</TD><TD>5</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/6/2024 00:00</TD><TD>8603308</TD><TD>10/7/2024 00:00</TD><TD>5</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>11/1/2024 00:00</TD><TD>8578294</TD><TD>10/7/2024 00:00</TD><TD>4</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>10/23/2024 00:00</TD><TD>8517792</TD><TD>10/7/2024 00:00</TD><TD>3</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>10/22/2024 00:00</TD><TD>8509909</TD><TD>10/7/2024 00:00</TD><TD>3</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>10/19/2024 00:00</TD><TD>8488369</TD><TD>10/7/2024 00:00</TD><TD>2</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>10/19/2024 00:00</TD><TD>8488358</TD><TD>10/7/2024 00:00</TD><TD>2</TD><TD>1</TD></TR><TR><TD>user3</TD><TD>10/9/2024 00:00</TD><TD>8425942</TD><TD>10/7/2024 00:00</TD><TD>1</TD><TD>1</TD></TR></TBODY></TABLE></DIV></DIV> Thu, 23 Jan 2025 17:31:03 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-format-latest-value-per-user/m-p/4378252#M173825 deaconb 2025-01-23T17:31:03Z Re: Conditional format latest value per user https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-format-latest-value-per-user/m-p/4378438#M173828 <P>Hello&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="144415" data-lia-user-login="deaconb" class="lia-mention lia-mention-user">deaconb</a>,</P> <P>&nbsp;</P> <P>Can you please try this approach:</P> <LI-CODE lang="markup">Latest Training Week = VAR MaxWeekPerUser = CALCULATE( MAX('Data'[Week of Training]), ALLEXCEPT('Data', 'Data'[USERNAME]) ) VAR IsLatestWeek = IF( MAX('Data'[Week of Training]) = MaxWeekPerUser, 2, -- Indicates the latest week 1 -- Indicates all other weeks ) RETURN IsLatestWeek </LI-CODE> Thu, 23 Jan 2025 20:19:49 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-format-latest-value-per-user/m-p/4378438#M173828 Sahir_Maharaj 2025-01-23T20:19:49Z Re: Conditional format latest value per user https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-format-latest-value-per-user/m-p/4378475#M173831 <P>Unfortunately this results in the same outcome for formatting as above where the expected result is the red boxes and green box being orange, but only the green box is orange.&nbsp;</P> Thu, 23 Jan 2025 20:53:59 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-format-latest-value-per-user/m-p/4378475#M173831 deaconb 2025-01-23T20:53:59Z Re: Conditional format latest value per user https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-format-latest-value-per-user/m-p/4378480#M173832 <P>Your measure and my original measure are basically the same. My measure is already working for assessing the latest week or not. I don't understand why it isn't being accepted as part of the conditional formatting.&nbsp;</P> Thu, 23 Jan 2025 20:55:04 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Conditional-format-latest-value-per-user/m-p/4378480#M173832 deaconb 2025-01-23T20:55:04Z