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

 

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.
     

3 Replies

  • 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