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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Target values do not match between tables

Hi,

 

I have two tables in the linked example below.  The first table accurately displays the target value that I want and expect for each product.

 

The second table displays the avg quantity sold for each product.  I would like to display the target value next to the avg quantity sold.  But I don't get the expected value for target on the second table.  The expected value is the target on this first table.

 

Can you help?

 

https://drive.google.com/open?id=11vzlQW0WF1g2jUvmLJNlPgNm3AGdcs7U

 

1 ACCEPTED SOLUTION
kentyler
Solution Sage
Solution Sage

Thanks for the sample file, makes everything a lot easier

Is this what you were looking for ?target.PNG
when you see a column returning all the same value it usually indicates some problem with the filter context

I wrote a measure to store the value of the current product, then use that value to lookup the target direct from the target table

Target Value =
VAR current_product = selectedvalue('Product Mix'[Product])
var result= lookupvalue(Target[Target Value],Target[Product],current_product)
return result
The filter context is a very powerful tool in DAX, but often you need to escape it... this is one of the reasons for starting to learn how to write measures. Hope this helps.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

  1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
  2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
  3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
  4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.
 




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

3 REPLIES 3
kentyler
Solution Sage
Solution Sage

Thanks for the sample file, makes everything a lot easier

Is this what you were looking for ?target.PNG
when you see a column returning all the same value it usually indicates some problem with the filter context

I wrote a measure to store the value of the current product, then use that value to lookup the target direct from the target table

Target Value =
VAR current_product = selectedvalue('Product Mix'[Product])
var result= lookupvalue(Target[Target Value],Target[Product],current_product)
return result
The filter context is a very powerful tool in DAX, but often you need to escape it... this is one of the reasons for starting to learn how to write measures. Hope this helps.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

  1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
  2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
  3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
  4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.
 




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

@kentylerthat's it!  thank you!

 

Boy do I still have a lot to learn when it comes to DAX and measures.  Got to start studying up on the VAR function.

 

thanks again.

depending on how your head works... take a look at the free lessons and other material on sqlbi.com

or look at avi sing's learnpowerbi videos on youtube

or search on youtube for curbal

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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