cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Measures in Filter

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
Highlighted
Resolver I
Resolver I

Re: Measures in Filter

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,
                                
                     

Highlighted
Helper I
Helper I

Re: Measures in Filter

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

Highlighted
Resolver I
Resolver I

Re: Measures in Filter

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/

 

Highlighted
Helper I
Helper I

Re: Measures in Filter

Can you reveal in my example?

 

Highlighted
Community Support
Community Support

Re: Measures in Filter

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.
Highlighted
Resolver I
Resolver I

Re: Measures in Filter

Is there no primary key on the Table1 ?

Highlighted
Helper I
Helper I

Re: Measures in Filter

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

Id_product repeat in Table 1

Highlighted
Helper I
Helper I

Re: Measures in Filter

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

Highlighted
Community Support
Community Support

Re: Measures in Filter

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.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors