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.
Hi, i need to do this condition on powerbi but dont know how to achieve this. The condition is get all IF columx=1 AND columx Maxx(from the last five rows) = 0 , 1 , 0). Basically what i am trying to say is when columx X = 1 needs to verify the last rows of columx and see if they are = 0 (if true then output will be 1 if false will be 0).
Time | ColumnX | Index | Output =2 | |
12/12/2012 01:00 | 0 | 1 | 0 | |
12/12/2012 01:10 | 0 | 2 | 0 | |
12/12/2012 01:20 | 0 | 3 | 0 | |
12/12/2012 01:30 | 0 | 4 | 0 | |
12/12/2012 01:40 | 0 | 5 | 0 | |
12/12/2012 01:50 | 1 | 6 | 1 | |
12/12/2012 02:00:00 | 1 | 7 | 0 | |
12/12/2012 02:10 | 0 | 8 | 0 | |
12/12/2012 02:20 | 1 | 9 | 0 | |
12/12/2012 02:30 | 0 | 10 | 0 | |
12/12/2012 02:40 | 0 | 11 | 0 | |
12/12/2012 02:50 | 0 | 12 | 0 | |
12/12/2012 03:00 | 0 | 13 | 0 | |
12/12/2012 03:10 | 0 | 14 | 0 | |
12/12/2012 03:20 | 1 | 15 | 1 |
Solved! Go to Solution.
So this should be something like:
Output Column = VAR __last5 = SUMX(FILTER('Table',[Index]<EARLIER([Index]) && [Index]>=EARLIER([Index])-5),[ColumnX]) RETURN IF([ColumnX] = 1 && __last5 = 0, 1, 0)
Hi @Anonymous
You can download the PBIX file here.
Below is the result I have got
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Hi @Anonymous
You can download the PBIX file here.
Below is the result I have got
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
So this should be something like:
Output Column = VAR __last5 = SUMX(FILTER('Table',[Index]<EARLIER([Index]) && [Index]>=EARLIER([Index])-5),[ColumnX]) RETURN IF([ColumnX] = 1 && __last5 = 0, 1, 0)
Did that work for you? i am testing mutiple ways but mostly the code is not compiling probably because the if the index is 1 he cant search 5 positons back because dont exist, is there a way to ignore that?
Have you tried the file I have shared? As this has worked for me. I have changed the value of first row for ColumnX to 1 still it is working for me
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
c
Cant understand for what reason the code is not compiling, i just add a new column and adpat the code to my case, and just say we are still working to solve the problem (loading ....), i alredy check my datasets to see if there is any value (1) before row 5 and dont exists. i dont know why this error is giving to me. if you knnow why i would be glad.
Is it possible for you to share the pbix file?
Both your aswers work, but when i tryed to compile in every dataset i have at the same time (it take infinite loading) the solution i got for this was to open a new powerbi file and send each data set individually to try see if i would get a error, and for my suprise everything was fine, but cant compile all at the same time weird 😕 , probably the problem will be on index when change between datasets who knows.
Thanks both for your fast support.
Best regards.
scmpm
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.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |