Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a requirement where I need to compare values from same column.
For example, assume below table
Here, I need to add a new column and compare the 'Value' column data with it's previous row value. If the current value is greater than the previous, then it should store 1 and if the current value is lesser than the previous, then it should store 0. (as below)
Please let me know how this can ben achieved.
Thanks!
Hi @KumarGS ,
You need to add the index column to Power Query first(Adding index columns is allowed).
If you also want to create group index with Power Query, please kindly refer to
How to create group index with Power Query or R
Then go back to Power BI Desktop, create a measure to get the previous value.
Pervious value = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),[Index]=MAX('Table'[Index])-1))
Now you can get your expected result by creating another measure.
New value = IF(ISBLANK([Pervious value]),BLANK(),IF([Pervious value]>SUM('Table'[Value]),0,1))
Also, you can get previous valu in Power Query, please kindly refer to
Value from previous row – Power Query, M language
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephen,
Thanks for your response. I tried to add an Index column at first and it was not getting supported in direct query mode. I am getting the below error and couldn't proceed further. Please advise.
Thanks!
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |