Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Grizman
Helper I
Helper I

Fields

I have two table:

 

 

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

 

1 ACCEPTED SOLUTION

First picture below shows bi-directional relationship between tables. 

p1.png

 

Second picture shows measure for year 2016. 

p2.png

 

Hope help you. 

Best regards,

View solution in original post

20 REPLIES 20
v-qiuyu-msft
Community Support
Community Support

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])

 

q3.PNG

 

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

 

Best Regards,
QiuyunYu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thyago_Rezende
Resolver I
Resolver I

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,
                                
                     

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

Is there no primary key on the Table1 ?

@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а?

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Thyago_Rezende

Table 1 aditional have two columns:

Id_transaction

Datetime

Id_product in first table don't unique.

Do you undestand me? 

Hello @Grizman,

 

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

 

 

11.PNG

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,

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.

First picture below shows bi-directional relationship between tables. 

p1.png

 

Second picture shows measure for year 2016. 

p2.png

 

Hope help you. 

Best regards,

Thanks a lot!


@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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Who can help?

Can anybody help me?

@v-ljerr-msft

Please help resolve my problem

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

Id_product repeat in Table 1

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/

 

Can you reveal in my example?

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.