Recover a field from facttable in another facttable

[jlarques]

Dear community,

I'm working on two fact tables and I want to recover one field from the table one into the table two.

Due that I have many relationships from both tables to dimension tables, the relationship between them is many to many filtering table two to table one.
I apply many filters to recover it but I don't know how to recover only the value. Now, I was applying many filters and I was using SUMX to recover the field, but the result is add all rows instead to recover each value from each row.

As you can see in the image below, first colum is fact table one that has values that I want to recover into table two. Second column is table two that add all rows, instead of recover each value.

I'm using this DAX code:

Obviously the result is summed instead of bringing each value. How can I recover each value by applying filters instead of summing them?

Thanks for your help.

José Luis 

[jlarques]

Hi Miguel,

apologies but I was flying and I didn't have my laptop.

Let me some time tomorrow morning to share the data with you.

[danextian]

Which specific column are you trying to get value from? Also, please take note that a DAX calc column cannot return more than one row so the result must be aggreated by concatenation, min, max, etc. So if your filter criteria returns more than one possible row, you must decide which of the resulting rows to pick.

[jlarques]

Hi @danextian ,
maybe this screenshot can help you to understand what I need:

Due to confidentiality I cannot share the table, but this is an extract of what I have in table 1 and what I want to recover in table 2. I hope this can clarify better my problem:

I filtered by "MES" because there are other values, but I'm only interested in this one. I want to recover value by value from table 1 not the result to add both values.

Thanks,

[danextian]

What do you mean by value by value? You mean the two rows from table1? Please note that calculated columns cannot return more than than a single row. The result must be aggregated one way or another which is essentially what SUMX is doing your formula.

[jlarques]

Yes, I want to recover 77,84€ and 12,97€ from table 1 to table2 in each row, because I have two rows in table 1 and same rows in table 2.

I think the problem is there is no difference betweem both rows to choose one of them.

[danextian]

You can't do that with calculated columns. You can return only a single row - either the first or the secone one but not both.

[jlarques · Accepted solution]

Ok. Understand. I try to find another way to calculate this value.

Thanks for your hep @danextian 

[jlarques]

Hi Danextian,,

apologies but I was flying and I didn't have my laptop.

Let me some time tomorrow morning to share the data with you.

[MFelix]

Hi @jlarques ,

Can you please share a sample data from both tables and expected result, in order to give the correct result is difficult to do so without the values you have.

I believe that you can use the lookupvalue function however my question is around the Fecha filters you are using.

[jlarques]

Hi @MFelix ,

Due to confidentiality I cannot share the table, but this is an extract of what I have in table 1 and what I want to recover in table 2. I hope this can clarify better my problem:

I filtered by "MES" because there are other values, but I'm only interested in this one. I want to recover value by value from table 1 not the result to add both values.

Thanks,