Forum Discussion

KumarGS's avatar
KumarGS
New Member
3 years ago

Compare values in same column - Direct query Power BI

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 previ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

 

 

 

KumarGS's avatar
KumarGS
New Member
3 years ago

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!