Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two table:
which reveal GrossTurnever=Q-ty*ProductPrice for every year and Margin=GrossTurnever2-GrossTurnever1
Solved! Go to Solution.
First picture below shows bi-directional relationship between tables.
Second picture shows measure for year 2016.
Hope help you.
Best regards,
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
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 ?
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
Table 1 aditional have two columns:
Id_transaction
Datetime
Id_product in first table don't unique.
Do you undestand me?
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.
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.
Second picture shows measure for year 2016.
Hope help you.
Best regards,
Thanks a lot!
@Grizman wrote:
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
Who can help?
Can anybody help me?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |