Previous Row Value with logical condition

Question

Hello,&nbsp;i need to create a DAX to have value from column CodiceDisegno if some value is true.&nbsp;&nbsp;If on column "Tipo" have code 3 write on new colum (name RicavaDisegno) the value from column "Codice Disegno".&nbsp;The problem are:- On row with tipo 3/9/275/281/276/279/278/4 i have blank "Codice Disegno"- I have multiple row at same date-time the filtre on row before must be for code on "Codice Macchina"For example:- Codice Macchina --&gt; 4717- Datetime Start 00.25 (IMES_Start) ha code "Tipo" --&gt;3- Codice Disegno --&gt; blank- New Column --&gt; write value row before. In this case the colum row before have code tipo 1 so i can use (00.00 - 00.24.59) --&gt; PAX065&nbsp;

talespin · Answer

hi&nbsp;lifesafari&nbsp;,&nbsp;It is better to do this in Power Query.&nbsp;I am sharing Calculated Column.Assumption : This logic works on assumption that there will always be a row with non blank value for&nbsp;"Codice Disegno" with startd date before the row(tipo 3/9/275/281/276/279/278/4) with blank or "0" for same "Codice Macchina" and Datetime Start.&nbsp;Calculated Column---------------------Column =VAR _machinecode = TestTable5[Codice Macchina]VAR _IMESStart = TestTable5[IMES_Start]returnIF( TestTable5[Tipo] IN {3,9,275,281,276,279,278,4},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(TestTable5[Codice Disegno]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; REMOVEFILTERS(TestTable5), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TestTable5[Codice Macchina] = _machinecode, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TestTable5[IMES_Start] &lt;= _IMESStart,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FORMAT(TestTable5[IMES_Start], "YYYY-MM-DD") = FORMAT(_IMESStart, "YYYY-MM-DD"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT(TestTable5[Tipo] IN {3,9,275,281,276,279,278,4}) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TestTable5[Codice Disegno])&nbsp;&nbsp;

lbendlin · Answer

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

lifesafari · Answer

It's same to be work. Let me some time to analyse to confirm as solution.During this time can i ask you wich is the logic that you have use to arrive at this DAX? Thanks&nbsp;

talespin · Answer

hi&nbsp;lifesafari&nbsp;Please see the comments(Put it in a notepad so its easy to read). Hope this helps. Let me know if any question.&nbsp;Calculated Column---------------------Column =//Since this is a calculated column, which invokes row context, it will iterate table row by row. I am storing current row values for below two columns in a variable.VAR&nbsp;_machinecode&nbsp;=&nbsp;TestTable5[Codice Macchina]VAR&nbsp;_IMESStart&nbsp;=&nbsp;TestTable5[IMES_Start]//Since you mentioned that "Codice Disegno" is blank or 0 where TestTable5[Tipo]&nbsp;is&nbsp;{3,9,275,281,276,279,278,4}, so I have put a condition so that CALCULATE is only executed if TestTable5[Tipo]&nbsp;is&nbsp;{3,9,275,281,276,279,278,4}//With CALCULATE, context transition is happening from row context to filter context(all the values of all columns in current row will translate to filters).//So in CALCULATE first I am removing all filters from table and then again applying filter only on "Codice Macchina" and "IMES_Start".//Since "IMES_Start" is datetime I am fetching only recods before current value of "IMES_Start".//Then I am making sure only data from current date is retrieve by using FORMAT(TestTable5[IMES_Start],&nbsp;"YYYY-MM-DD") =&nbsp;FORMAT(_IMESStart,&nbsp;"YYYY-MM-DD")//THen I am excluding all records where TestTable5[Tipo]&nbsp;is&nbsp;{3,9,275,281,276,279,278,4}//Finally I get the MAX of "Codice Disegno"returnIF(&nbsp;TestTable5[Tipo]&nbsp;IN&nbsp;{3,9,275,281,276,279,278,4},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;MAX(TestTable5[Codice Disegno]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;REMOVEFILTERS(TestTable5),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;TestTable5[Codice Macchina]&nbsp;=&nbsp;_machinecode,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;TestTable5[IMES_Start]&nbsp;&lt;=&nbsp;_IMESStart,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;FORMAT(TestTable5[IMES_Start],&nbsp;"YYYY-MM-DD") =&nbsp;FORMAT(_IMESStart,&nbsp;"YYYY-MM-DD"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NOT(TestTable5[Tipo]&nbsp;IN&nbsp;{3,9,275,281,276,279,278,4})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;TestTable5[Codice Disegno])

Forum Discussion

Previous Row Value with logical condition

4 Replies

Related content

Slicer by logic conditions

Logic problem?

FX Conversion Logic

Custom Tooltip Not Passing Single-Country Filter Context When Y Axis Uses Conditional Logic

Logical Conditions