locked
Linked tables and data not displaying RRS feed

  • Question

  • Hi Everyone

    I have linked three tables in Powerpivot: one data/fact-like table and two connected reference tables (one for judges and one for month/year combinations). The reference tables are each joined to the data table.

    The data table contains a judge ID for each record in the data table and the judge reference table contains the judge ID and name. There is an 'all judges' value in the judge table that is just the count of all records in the data table.

    Prior to breaking out the month/year table, it was in the data table and the 'all judges' value displayed properly. However, since I broke out the month/year table, the 'all judges' value is no longer displaying in the table. So I obviously don't understand why this is happening?

    Below are the table names and one of the formulas for the table and a screenshot. Also, the display table contains the year and exitmonthcategory variables from the Time table and the judge name and title from the S1JudgeIds table. I have two judges selected: one individual judge and 'all judges' however, only the individual judge is displaying.  Any insight would be appreciated!

    Paul

    Data/fact table: s1Perm1

    Judge table: s1JudgeIds

    Year/month table: Time

    Formula: =CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit]),ALL(Time[ExitMonthCategory]))




    • Edited by Paul-NYS Wednesday, July 1, 2015 3:50 PM
    Wednesday, July 1, 2015 3:42 PM

Answers

All replies

  • Hi Paul,

    According to your description, you are experiencing the issue when link multiple tables, right?

    In PowerPivot, if we need to display the value on another table, we can use LOOKUPVALUE function which returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value. Please refer to the links below to see the details.
    https://msdn.microsoft.com/en-us/library/gg492170.aspx
    https://thedataspecialist.wordpress.com/2013/02/16/equivalent-of-vlookup-in-daxpart-i/

    In your scenario, what do you mean by "However, since I broke out the month/year table, the 'all judges' value is no longer displaying in the table"? It's hard for us to understand your issue and requirement, please provide us more detail information about it, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, July 10, 2015 1:59 AM
  • Hi Charlie

    In the initial post above, there is a table that shows one judge data--that is what I am referring to. Since I split both the 'entrycohortyear' and 'exitmonth' columns (that are displayed above) in separate tables, linked by a common field, the 'all judges' value that is stored in another separate, but linked table, is not appearing in the above table.

    So I now have a base data table that is linked to two separate dictionary tables: one for judges and one for time values. As I stated above, since I broke the above time values into the new, separate, but linked table, the 'all judges' value is no longer displaying.

    Paul

    Monday, July 13, 2015 2:26 PM
  • Paul,

    Please send a Frown to the team or use Power BI User Voice to submit your feedback!

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, July 29, 2016 8:55 PM