Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 in the sequence as a Y or else N in a new column
So the logic in the new colum would be
when new record is available(e.g index 35)
the values in the new column at each row would be applying the following logic
If SW_H_B in index - 1 = Y
and SW_H_B in Index 0 = Y
and SW_H_B in Index +1 = N
and SW_H_B in index + 2 = N
then Y or else N
Column computed value is expected result marked in red.
Many thanks for your help.
Index | SW_H_B | Computed value |
0 | N | N |
1 | N | N |
2 | N | N |
3 | N | N |
4 | N | N |
5 | Y | N |
6 | Y | N |
7 | N | N |
8 | Y | N |
9 | N | N |
10 | Y | N |
11 | N | N |
12 | N | N |
13 | N | N |
14 | Y | N |
15 | N | N |
16 | N | N |
17 | N | N |
18 | Y | N |
19 | Y | N |
20 | Y | N |
21 | Y | Y |
22 | N | N |
23 | N | N |
24 | Y | N |
25 | N | N |
26 | N | N |
27 | N | N |
28 | N | N |
29 | N | N |
30 | Y | N |
31 | N | N |
32 | Y | N |
33 | Y | Y |
34 | N | N |
35 | N | N |
Solved! Go to Solution.
Hi @sabin_arsenal ,
Try this code:
Hi @sabin_arsenal ,
Try this m code:
try
if #"Changed Type1"{[Index]-1}[SW_H_B] = "Y" and
[SW_H_B] = "Y" and
#"Changed Type1"{[Index]+1}[SW_H_B] = "N"
and #"Changed Type1"{[Index]+2}[SW_H_B] = "N"
then "Y" else "N"
otherwise "N"
Replace the #"Changed Type1" with your last step name on Power Query.
Hi @sabin_arsenal ,
Try this code:
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.
Thank you!
will try and let u know, thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.