Display aggregates of two tables in one table visual

[Manuel0814]

Hi,

I have the following two tables imported into Power BI Desktop which are linked by a foreign key constraint.

The first table "pbi Parent" contains four rows:

The second table "pbi Child" contains also four rows, which are referenced to the second row of the parent table:

Now I have tried to display data of the parent and the child table in a single table visual. From the parent table I need to have the columns id, parent_name and a sum over parent_value. From the child table I need a sum over the child_value as well as the child_name.

When I set the column "parent_value" to "don't summarize", the table looks like this:

However, when I set the column "parent_value" to SUM, the table looks like this:

Can someone explain to me why the parent_values for the first, third and fourth rows are filtered out here? And why is the total sum still correct? I was reading the documentation but I am afraid I am missing something here. I am curious about why Power BI removes the values from the parent table even though the filter direction is the other way round.

Kind regards,

Manuel

[DataZoe]

@Manuel0814 If you care the parent_value and child_value as explicit measures, they work as expected:

So using the performance analyzer you can see the queries:

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Second Child"}, 'pbi Child'[child_name])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('pbi Parent'[id], 'pbi Parent'[parent_name], 'pbi Child'[child_name]), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      "pv", 'pbi Parent'[pv],
      "cv", 'pbi Parent'[cv]
    )

  VAR __DS0PrimaryShowAllCompat = 
    ADDCOLUMNS(
      KEEPFILTERS(
        CALCULATETABLE(
          FILTER(
            KEEPFILTERS(
              GENERATEALL(
                KEEPFILTERS(VALUES('pbi Parent'[id])),
                GENERATEALL(
                  KEEPFILTERS(
                    FILTER(
                      KEEPFILTERS(VALUES('pbi Parent'[parent_name])),
                      OR(
                        OR(
                          CALCULATE(NOT(ISEMPTY('pbi Parent'))),
                          CALCULATE(NOT(ISEMPTY('pbi Child')))
                        ),
                        NOT(
                          ISEMPTY(
                            FILTER(
                              KEEPFILTERS(VALUES('pbi Child'[child_name])),
                              OR(
                                NOT(ISBLANK('pbi Parent'[pv])),
                                NOT(ISBLANK('pbi Parent'[cv]))
                              )
                            )
                          )
                        )
                      )
                    )
                  ),
                  FILTER(
                    KEEPFILTERS(VALUES('pbi Child'[child_name])),
                    OR(
                      OR(
                        CALCULATE(NOT(ISEMPTY('pbi Child'))),
                        NOT(ISBLANK('pbi Parent'[pv]))
                      ),
                      NOT(ISBLANK('pbi Parent'[cv]))
                    )
                  )
                )
              )
            ),
            OR(
              OR(
                OR(
                  OR(
                    NOT(ISBLANK('pbi Parent'[id])),
                    NOT(ISBLANK('pbi Parent'[parent_name]))
                  ),
                  NOT(ISBLANK('pbi Child'[child_name]))
                ),
                NOT(ISBLANK('pbi Parent'[pv]))
              ),
              NOT(ISBLANK('pbi Parent'[cv]))
            )
          ),
          KEEPFILTERS(__DS0FilterTable)
        )
      ),
      "IsGrandTotalRowTotal", FALSE
    )

  VAR __DS0PrimaryReordered = 
    SELECTCOLUMNS(
      KEEPFILTERS(__DS0Core),
      "'pbi Parent'[id]", 'pbi Parent'[id],
      "'pbi Parent'[parent_name]", 'pbi Parent'[parent_name],
      "'pbi Child'[child_name]", 'pbi Child'[child_name],
      "IsGrandTotalRowTotal", [IsGrandTotalRowTotal]
    )

  VAR __DS0PrimaryShowAll = 
    NATURALLEFTOUTERJOIN(
      SUMMARIZE(
        UNION(__DS0PrimaryReordered, __DS0PrimaryShowAllCompat),
        'pbi Parent'[id],
        'pbi Parent'[parent_name],
        'pbi Child'[child_name],
        [IsGrandTotalRowTotal]
      ),
      __DS0Core
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0PrimaryShowAll,
      [IsGrandTotalRowTotal],
      0,
      'pbi Parent'[id],
      1,
      'pbi Parent'[parent_name],
      1,
      'pbi Child'[child_name],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'pbi Parent'[id],
  'pbi Parent'[parent_name],
  'pbi Child'[child_name]

and with the measures:

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Second Child"}, 'pbi Child'[child_name])

  VAR __DS0Core = 
    SELECTCOLUMNS(
      KEEPFILTERS(
        FILTER(
          KEEPFILTERS(
            SUMMARIZECOLUMNS(
              ROLLUPADDISSUBTOTAL(
                ROLLUPGROUP('pbi Parent'[id], 'pbi Parent'[parent_name], 'pbi Child'[child_name]), "IsGrandTotalRowTotal"
              ),
              __DS0FilterTable,
              "Sumparent_value", CALCULATE(SUM('pbi Parent'[parent_value])),
              "Sumchild_value", CALCULATE(SUM('pbi Child'[child_value])),
              "HasDatapbi_Child", IGNORE(
                CALCULATE(NOT(ISEMPTY('pbi Child')))
              )
            )
          ),
          OR(NOT(ISBLANK([Sumchild_value])), [HasDatapbi_Child])
        )
      ),
      "'pbi Parent'[id]", 'pbi Parent'[id],
      "'pbi Parent'[parent_name]", 'pbi Parent'[parent_name],
      "'pbi Child'[child_name]", 'pbi Child'[child_name],
      "IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
      "Sumparent_value", [Sumparent_value],
      "Sumchild_value", [Sumchild_value]
    )

  VAR __DS0PrimaryShowAllCompat = 
    ADDCOLUMNS(
      KEEPFILTERS(
        CALCULATETABLE(
          FILTER(
            KEEPFILTERS(
              GENERATEALL(
                KEEPFILTERS(VALUES('pbi Parent'[id])),
                CALCULATETABLE(
                  GENERATEALL(
                    KEEPFILTERS(VALUES('pbi Parent'[parent_name])),
                    CALCULATETABLE(
                      FILTER(
                        KEEPFILTERS(VALUES('pbi Child'[child_name])),
                        OR(
                          CALCULATE(NOT(ISEMPTY('pbi Child'))),
                          NOT(
                            ISBLANK(CALCULATE(SUM('pbi Parent'[parent_value])))
                          )
                        )
                      )
                    )
                  )
                )
              )
            ),
            OR(
              OR(
                OR(
                  NOT(ISBLANK('pbi Parent'[id])),
                  NOT(ISBLANK('pbi Parent'[parent_name]))
                ),
                NOT(ISBLANK('pbi Child'[child_name]))
              ),
              NOT(
                ISBLANK(CALCULATE(SUM('pbi Parent'[parent_value])))
              )
            )
          ),
          KEEPFILTERS(__DS0FilterTable)
        )
      ),
      "IsGrandTotalRowTotal", FALSE
    )

  VAR __DS0PrimaryReordered = 
    SELECTCOLUMNS(
      KEEPFILTERS(__DS0Core),
      "'pbi Parent'[id]", 'pbi Parent'[id],
      "'pbi Parent'[parent_name]", 'pbi Parent'[parent_name],
      "'pbi Child'[child_name]", 'pbi Child'[child_name],
      "IsGrandTotalRowTotal", [IsGrandTotalRowTotal]
    )

  VAR __DS0PrimaryShowAll = 
    NATURALLEFTOUTERJOIN(
      SUMMARIZE(
        UNION(__DS0PrimaryReordered, __DS0PrimaryShowAllCompat),
        'pbi Parent'[id],
        'pbi Parent'[parent_name],
        'pbi Child'[child_name],
        [IsGrandTotalRowTotal]
      ),
      __DS0Core
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0PrimaryShowAll,
      [IsGrandTotalRowTotal],
      0,
      'pbi Parent'[id],
      1,
      'pbi Parent'[parent_name],
      1,
      'pbi Child'[child_name],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'pbi Parent'[id],
  'pbi Parent'[parent_name],
  'pbi Child'[child_name]

The biggest difference seems to be in the first variable, with more added on the implicit measures version:

And then also on the second variable for the explicit measures version:

And it looks like that first one __DS0Core is where the limitation on the implicit measure happens.

and if I remove the NOT(...) on line 23 the results are the same as the explicit measure version:

so, all this to say, the way the table is built differs from when you use explicit measures (create a measure) and implicit measures (putting a value in the table and choosing sum as the aggregation) which is what is causing the different behavior.  I am not sure why that is though! 

What is also interesting is that a matrix doesn't have this trouble:

Also, when you remove the Child Name from the table, the implicit sum of values will show as expected:

[Manuel0814]

Thank you for your detailed reply. The solution is not quite right though, as you have the child_name "Second child" included for every parent, even if it does not belong to it. In my example I have changed the child_names to "a", "b", "c", "d". Then I get the following result (left one with explicit measures, right one with implicit):

I have defined the measures as follows:

cv = sum(child[child_value])
pv = sum('parent'[parent_value])

Do you know why there are these extra rows where Power BI combines e.g. the first parent and the first child?

Kind regards,

Manuel

[DataZoe]

@Manuel0814 I think the extra rows show becuase you have "Show items with no data" turned on.