Fields

[Grizman]

I have two table:

which reveal GrossTurnever=Q-ty*ProductPrice for every year and Margin=GrossTurnever2-GrossTurnever1

[v-qiuyu-msft]

Hi @Grizman,

In your scenario, please open Query Editor, rename columns in second table to 2015, 2016, 2017, then select all these three columns, click Unpivot Columns button, apply the changes.

Then create a measure:

Diff =
var varMAX=MAXX(ALLSELECTED(Table2[Year]),[Year])
var varMin=MINX(ALLSELECTED(Table2[Year]),[Year])
return
(LOOKUPVALUE(Table2[Value],'Table2'[Year],varMAX,'Table2'[Id_product],MAX('Table1'[Id_product])) - LOOKUPVALUE(Table2[Value],'Table2'[Year],varMin,'Table2'[Id_product],MAX('Table1'[Id_product])))*MAX('Table1'[Q-ty])

For details, you can download attached pbix file to have a look.

Best Regards,
QiuyunYu

[Thyago_Rezende]

Hi,

First, you need to transform both columns year and its values in 2 columns only. 

After that you can use:
GrossTurneverYear2016 = 
SUMX  ( 
             FILTER (

                          Table#2;
                          PriceYear = 2016
             );
             PriceValue2016 * RELATED ( Table#1[Q-ty])
)

Regards,
                                
                     

[Grizman]

If I transform both columns year and its values in 2 columns I will get many to many relationship because id_product in first and second table will repeat

[Thyago_Rezende]

Is there no primary key on the Table1 ?

[Grizman]

@Thyago_Rezende

In you recommended case scenario after unpivoted colums I cann't create relationship beetween two table.

Because column Id_product in Table 1 and after unpivoted column Id_product in Table 2 will not unique .

In Table 1 primary key is id_transaction.

Have your new ideа?

[v-qiuyu-msft]

Hi @Grizman,

Please review my previous post, I have created a pbix file based on the sample data in your original post. 

What's the problem in your scenario now? I didn't see the id_transaction in your table data, please clarify sample data and your requirement. 

Best Regards,
Qiuyun Yu 

[Grizman]

@Thyago_Rezende

Table 1 aditional have two columns:

Id_transaction

Datetime

Id_product in first table don't unique.

Do you undestand me? 

[Thyago_Rezende]

Hello @Grizman,

Can you send sample data? Or display all fields from tables?

[Grizman]

[Thyago_Rezende]

You must create a Bridge Table for relation both Table1 and Table2. A bridge table with PrimaryKey from id_Product and bi-directional relationship. 
This article below, explain about pattern works for many to many relationship. 

https://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-tab...

Regards,

[Grizman]

I created bridge table Table 3 with one column id_product.

But I don't know how realize formula Q-ty*Price across thase table.

[Thyago_Rezende · Accepted solution]

First picture below shows bi-directional relationship between tables. 

Second picture shows measure for year 2016. 

Hope help you. 

Best regards,

[Grizman]

Thanks a lot!

[v-qiuyu-msft]

---

@Grizman wrote:

@Thyago_Rezende

Table 1 aditional have two columns:

Id_transaction

Datetime

Id_product in first table don't unique.

Do you undestand me? 

---

Can you share sample data of those two tables and your expected results? 

Best Regards,
Qiuyun Yu 

[Grizman]

Who can help?

[Grizman]

Can anybody help me?

[Grizman]

@v-ljerr-msft

Please help resolve my problem

[Grizman]

In Table 1 primary key is Id_transaction, but not a Id_product.

Id_product repeat in Table 1

[Thyago_Rezende]

You will can use Treatas pattern. It provides virtual relationship between tables.

Try do this: 

http://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[Grizman]

Can you reveal in my example?