cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lipebello Frequent Visitor
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:

 

IDVALUEREFNEW COLUMN
1230 0
124101230
12520 0
126301230
12740 0
1285012410
12960 0
1307012410
13180 0
1329012520
133100 0
13411012520
135120 0

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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.

 

Untitled.png

6 REPLIES 6
Super User
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
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
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.

 

lookupvalue.PNG

 

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
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.

 

Untitled.png

lipebello Frequent Visitor
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.
Capture.PNG


Super User
Super User

Re: search for a value in another row.

You are welcome.