Milwaukee "The Brew City" PUG

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Mike's June Riddle

  • 1.  Mike's June Riddle

    Bronze Contributor
    Posted Jun 27, 2018 10:28 AM

    I thought I would have a better go at explaining what I thought was going on with Mike's riddle yesterday. This is actually a really good example of how DAX and filtering works. For those that missed it, there were two tables with a one to many join, that looked a bit like this:
    Notice here the filter direction - it is one way from table1 (one side) to table2 (many side).
    There were also two measures, one the sum of Table1[value], the other the sum of Table2[number].
    This brings the first import point, that although the measures were stored in their respective table, this is purely a visual thing. A measure, does not live in any specific table and can be stored anywhere. This is because a measure has no row context. This means, unless explicitly stated, it will refer to the table as a whole an not a specific row.

    A calculated column will always have row context, and live in the table. For example if we made a calculated column of " Table1[value] + 1" it would look like this:

    The highlighted row has a row context of table1[Name] = B, meaning the value in the calculated column will always have a value for this row. A measure, however, is not stored like this and will only have a row context if we give one. The measure sum(Table1[value]) has a value of 131, and will be stored as only one result, unless given row context.

    One way to give a row context is to add it to our table. Lets make a new table, Table3, which we built in report view. Here, table3, I add table1[name], table2[name], and our measure Number, which is sum(Table2[number]) .

    tKYKLm8IRCSlWmkTmiku_pbi2.PNGI just returned the first row of table3, but what you can see is that now we are giving our measure row context. What we are implicitly saying, is:

    sum(Table2[number]), WHERE Table2[name] is "a" AND Table1[name] is "a"

    What happens is that Table1 looks at a, filters table2 for just a, and returns now just a in table2, which it sums and provides the correct answer.

    However, when we add the second measure valuesum(Table1[value]), it returns all values from table2 for every value in table1.
    Now, we are saying :
    Table1[value]), WHERE Table2[name] is "a" AND Table1[name] is "a"

    However, our end result sum(Table1[value]), is at the other side of a one way relationship. Because we have both table2[name] and table1[name], table2[name] filters for "a", but it cannot filter table1. Therefore, for each "a" in table2, it returns the whole of table1, as there is no way to filter this. 

    The solution

    One way is to create a two way relationship, but this is not a good idea and may cause things to break. The best way, Mike's solution, was to create a Dimension table which contains only the Names.
    Really, what we've done is join two fact tables (they both have facts, number and value), but it just so happened that table1 did not have any repeats so this became the "one" side. The best way would to have a third dimension table, then use the "Name" column in this table to create our table3.


    Steven Campbell
    Senior Analyst
    Milwaukee Public Schools

  • 2.  RE: Mike's June Riddle

    Bronze Contributor
    Posted Jun 27, 2018 10:37 AM
    Excellent recap, Steve!

    It was interesting to see multiple ways (yours and Mike's) to handle the Cartesian product in Power BI.


    Mark Baich