cancel
Showing results for
Did you mean:
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...

Proud to be a Datanaut!

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,

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.

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.