Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 so...
Need Help
  • kentyler's avatar
    kentyler
    6 years ago

    Thanks for the sample file, makes everything a lot easier

    Is this what you were looking for ?
    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.
     
kentyler's avatar
kentyler
Solution Sage
6 years ago

Thanks for the sample file, makes everything a lot easier

Is this what you were looking for ?
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.
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

    • kentyler's avatar
      kentyler
      Solution Sage
      6 years ago

      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