Forum Discussion

jarthda's avatar
jarthda
Regular Visitor
6 years ago

Foreign keys in s SQL model

Should I sum an integer in a parent table based on category in a child table?  This happens automatically in when I sum from a category already in the parent table but does not seem to work for categories in the child table. Is there something I must do? 

 

Need Help
Tutorial Requests

3 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi jarthda ,

    I have created two tables and a measure to try to get it:

    parent tablechild table

    Measure =
SUMX (
    SUMMARIZE (
        'Parent table',
        'Parent table'[ID],
        'Parent table'[Group],
        'Parent table'[Value1],
        'Parent table'[Value2]
    ),
    VAR _id = 'Parent table'[ID]
    VAR _group = 'Parent table'[Group]
    RETURN
        CALCULATE (
            SUM ( 'Parent table'[Value1] ),
            FILTER (
                'Child table',
                'Child table'[Group] = _group
                    && 'Child table'[ID] = _id
            )
        )
)

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • jarthda's avatar
      jarthda
      Regular Visitor
      6 years ago

      Thank you for this solution. .I see it takes quite a bit in Power BI to agregate across a well defined SQL Schema. I'm wondering why don't I just do it in SQL?  Let me play with it for a while and see how it works. Thanks so much. i will vote up after.

       

       

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    jarthda , The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.