cancel
Showing results for
Did you mean:
lipebello Frequent Visitor

## search for a value in another row.

Hello,

I'm learning DAX and want to do something like the SUMIFS on excel. Basically i want to create another column that calculates the VALUE When the ID = REF. Like this:

 ID VALUE REF NEW COLUMN 123 0 0 124 10 123 0 125 20 0 126 30 123 0 127 40 0 128 50 124 10 129 60 0 130 70 124 10 131 80 0 132 90 125 20 133 100 0 134 110 125 20 135 120 0

1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: search for a value in another row.

Hi,

Try this calculated column formula

=IF(ISBLANK(CALCULATE(SUM(Data[VALUE]),FILTER(Data,Data[ID]=EARLIER(Data[REF])))),0,CALCULATE(SUM(Data[VALUE]),FILTER(Data,Data[ID]=EARLIER(Data[REF]))))

Hope this helps. 6 REPLIES 6 Super User

## Re: search for a value in another row.

You want to use EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

lipebello Frequent Visitor

## Re: search for a value in another row.

Thanks for the Answer Greg.  i'm trying to find the right sintax to make it work, because i`m getting sintax error Community Support Team

## Re: search for a value in another row.

Hi @lipebello,

What about using Lookup value?

You could create a calculated column with the formula below.

`Column = IF(LOOKUPVALUE('Table'[VALUE],'Table'[ID],'Table'[REF])=0,0,LOOKUPVALUE('Table'[VALUE],'Table'[ID],'Table'[REF]))`

Here is the output. If you still need help, you could show your formula and the error message so that we could help further on it.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Super User

## Re: search for a value in another row.

Hi,

Try this calculated column formula

=IF(ISBLANK(CALCULATE(SUM(Data[VALUE]),FILTER(Data,Data[ID]=EARLIER(Data[REF])))),0,CALCULATE(SUM(Data[VALUE]),FILTER(Data,Data[ID]=EARLIER(Data[REF]))))

Hope this helps. lipebello Frequent Visitor

## Re: search for a value in another row.

@v-piga-msft, thanks for the reply. it worked only with a single value, but when i add more equal ids, it gives me the "multiple results"message.

@Ashish_Mathur Thanks! it worked.  Super User

You are welcome.