Forum Discussion
Solved
If and then statement between rows (finding a sequence)
Hello, I am new to Power bi and wondering if you could help with some problem solving. I have a data set and below are a part of it, I am looking for a sequence - YYNN and Marking the second Y ...
Hi sabin_arsenal ,
Try this code:
Column =VAR _index = 'Table'[Index]VAR _sw_h_b = 'Table'[SW_H_B]VAR _p1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index - 1))VAR _n1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 1))VAR _n2 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 2))RETURN IF(_p1 = "Y" && _sw_h_b = "Y" && _n1 = "N" && _n2 = "N"; "Y"; "N")
Hi sabin_arsenal ,
Try this code:
Column =
VAR _index = 'Table'[Index]
VAR _sw_h_b = 'Table'[SW_H_B]
VAR _p1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index - 1))
VAR _n1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 1))
VAR _n2 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 2))
RETURN IF(_p1 = "Y" && _sw_h_b = "Y" && _n1 = "N" && _n2 = "N"; "Y"; "N")
That worked like a charm, many thanks. Dont mind me asking, but would u mind quickly explaining the logic behind the code? VAR function is variance?
Hi sabin_arsenal ,
VAR is a keyword to declare variables in DAX.
Column =VAR _index = 'Table'[Index] // get the current indexVAR _sw_h_b = 'Table'[SW_H_B] // get the current value for SW_H_B columnVAR _p1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index - 1)) // SW_H_B value for previous rowVAR _n1 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 1)) // SW_H_B value for next rowVAR _n2 = CALCULATE(DISTINCT('Table'[SW_H_B]); FILTER('Table'; 'Table'[Index] = _index + 2)) // SW_H_B value for current + 2 rowRETURN IF(_p1 = "Y" && _sw_h_b = "Y" && _n1 = "N" && _n2 = "N"; "Y"; "N") comparing the valuesThe distinct function plays the important role here, it returns the scalar value for the search.I hope I made it clear, let me know if no.Thank you!