Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Manuel0814
Frequent Visitor

Display aggregates of two tables in one table visual

Hi,

 

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

Manuel0814_0-1634762808403.png

The first table "pbi Parent" contains four rows:

Manuel0814_1-1634762851095.png

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

Manuel0814_3-1634762902157.png

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:

Manuel0814_4-1634763099664.png

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

Manuel0814_5-1634763145845.png

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

 

3 REPLIES 3
DataZoe
Microsoft Employee
Microsoft Employee

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

DataZoe_0-1634771050523.png

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:

DataZoe_1-1634773341944.png

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

DataZoe_2-1634773512171.png

 

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

 

DataZoe_3-1634773619007.png

 

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

DataZoe_0-1634775382882.png

 

 

DataZoe_5-1634774061395.pngDataZoe_6-1634774085601.png

 

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:

DataZoe_7-1634774583861.png

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

 

DataZoe_0-1634774902668.png

 

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

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

Manuel0814_1-1634798629866.png

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

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.