none
Sum of a Measure from a Parent Fact in to a child fact

    Question

  • I have 2 Facts Parent Fact and the Child Fact with 1 to many relationship. I have define the relationship between the 2 facts. For better user experience i wanted to bring Parent Fact Measure into Child Fact measure group instead of having 2 different measure group. 

    Thursday, July 24, 2014 7:23 PM

Answers

  • What you have experienced is a common anomaly in the Tabular model DAX designer.  The designer sometimes uses the old object names for comparison when it updates names.  Most likely it had not yet recorded the effective "delete" when you copied and pasted the measure to the new table.  Try this:

    1. In the first table, select and copy the measure definition to the clipboard
    2. Rename this measure and put the text "Old " in front of the name (i.e. "My Measure" becomes "Old My Measure").  Press the enter key and wait for the progress bar in the bottom right of the designer to show that the object name is updated.
    3. Paste the definition in the measures areas in the second table.  Press Enter and wait for the update, which should be successful.
    4. Delete the first measure, "Old My Measure", from the first table and press enter.

    Tedious, I know, but that's just the way the designer works right now.  I've demonstrated this to the product team and they have told us that they're working on improvements.


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    Sunday, July 27, 2014 6:45 AM
    Moderator

All replies

  • To clarify, you have two tables and two measures. All you want to do is move one measure from one table to another so both measures exist in one table?

    You can simply select the measure in the measure grid from the Power Pivot window, cut it, and paste it into the measure grid of a different table.

    Alternately, you can open up your "Manage Calculated Fields" dialog box from the Excel window, select your measure, click "Edit", and choose the table you'd like the measure to live in. An image is included below.

    Edit: In general, you can create any measure in any table, even in a table that has no columns involved in the measure calculation. If you'd like to, you can create a 1x1 table to hold all measures, then hide the column so you have an entity that appears to client applications as having only measures in it.



    • Edited by Greg2178 Thursday, July 24, 2014 9:20 PM typo
    Thursday, July 24, 2014 7:34 PM
  • Thanks Greg, i think i post the question in Power Pivot . I am doing this in SSAS Tabular model but the idea is same. But i took the idea and wrote a simple DAX in table2( the child one) Total :=Sum(table1.column1) i get an error saying it already exists(There is a measure in table1 Total := Sum(table1.column1)) . But if i remove the measure from table1 i am able to get that in table2 which make sense. 
    Thursday, July 24, 2014 8:53 PM
  • This is the appropriate behavior. 

    Measure names must be unique across the model, whereas column names need only be unique within a table.

    I should have said cut and paste in my response - I am editing it to correct that error.

    Thursday, July 24, 2014 9:19 PM
  • What you have experienced is a common anomaly in the Tabular model DAX designer.  The designer sometimes uses the old object names for comparison when it updates names.  Most likely it had not yet recorded the effective "delete" when you copied and pasted the measure to the new table.  Try this:

    1. In the first table, select and copy the measure definition to the clipboard
    2. Rename this measure and put the text "Old " in front of the name (i.e. "My Measure" becomes "Old My Measure").  Press the enter key and wait for the progress bar in the bottom right of the designer to show that the object name is updated.
    3. Paste the definition in the measures areas in the second table.  Press Enter and wait for the update, which should be successful.
    4. Delete the first measure, "Old My Measure", from the first table and press enter.

    Tedious, I know, but that's just the way the designer works right now.  I've demonstrated this to the product team and they have told us that they're working on improvements.


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    Sunday, July 27, 2014 6:45 AM
    Moderator
  • Here's something to think about, Moyz, Greg

    The table that houses a measure is simply a container, a place to keep any aggregated or calculated values that can get their expression values from other fields and tables anywhere in the model.  You don't have to organize them into the fact table, dimension/lookup tables or any other container that may or may not serve as the logical home for this measure thing.  My point is that you can just make up your own containers for these things.  I will commonly create a blank and unrelated table called Metrics and put all of the otherwise homeless measure in that table.  After the database is processed, every table "box" around calculated measures gets branded by the SSAS metadata sorting hat.  But rather than arbitrarily assigning some one to Slytherin or Gwinded way of saying that you could define an empty table based on any simple query just to get the table to show in te designer and then use it as an empty container for all  measures.

    We use this technique all the time to reorganize measure group and measures.


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    Sunday, July 27, 2014 7:10 AM
    Moderator