search for a value in another row.

[Anonymous]

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

Thanks in advance.

[Ashish_Mathur · Accepted solution]

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.

[Greg_Deckler]

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

[Anonymous]

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

[v-piga-msft]

Hi @Anonymous,

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

[Anonymous]

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

[Ashish_Mathur]

You are welcome.