Power Pivot relations

[bartvana]

This is somehow related to my previous question "Basic Power Pivot question (why does the relationship "not work")", but I have a similar problem in a new file (see attached).

I have a fact table "ItemVendor" and a dimension table "CompDetailAll", with a shared field "SKU".

Dimtable "CompDetailAll":

Fact table "ItemVendor":

Relation:

When I make a pivot table, I can't get it right. If I grab the SKU from the dimtable, I get the following problem:

If I grab the SKU from the fact table, I have the following problem:

I can solve this by looking up the dim values in the fact table, or vice versa, but what's the use of relations if I have to do that? I'm a bit confused here...

[#2 · Accepted solution]

@bartvana All credit should go to @Sergei Baklan , for the solution in the thread you linked. I just took the same approach to create the following measure that I called "DA":

=CALCULATE(
   SUM(tCompDetailAll[StDevAvg]),
   TREATAS (
      VALUES(tItemVendor[SKU]),
      tCompDetailAll[SKU]
   )
)

[#3]

@bartvana 

Your desired result is

Actually you aggregate both by SKU and Supplier, even if Supplier is not shown here.

As variant you may create two measures

DelayDays:=SUM(tItemVendor[DelTermDays])

and

StDev:=IF( ISBLANK([DelayDays]) , blank(),  SUM(tCompDetailAll[StDevAvg]) )

They give

As a comment, always hide field on many side of relationships, it helps to avoid some errors

[#4]

@Sergei Baklan @Riny_van_Eekelen Thank you for your answers.

It still isn't really clear to me however, but maybe this is outside of the scope of this forum. In the TREATAS reference page it says (and Sergei also said this in answer to my other question:( "Best for use when a relationship does not exist between the tables." But I have a relationship between the tables. What is the use then of such a relationship if I still have to make measures with the same relationship defined in them again?

[#5]

@bartvana 

That's since we try to receive result using many-to-one relationship, which is not supported in Excel directly. Same result as with TREATAS() we may receive using physical relationship, but with measure using CROSSFILTER()

StDevR :=
CALCULATE (
    SUM ( tCompDetailAll[StDevAvg] ),
    CROSSFILTER ( tItemVendor[SKU], tCompDetailAll[SKU], BOTH )
)

In this case we have no SupplierID in PivotTable but DelTermDays are not aggregated, they are used as labels

[#6]

@Sergei Baklan OK, so for everything that's many-to-one you need to write a measure, that's more or less it, right?

That's good to know, I thought that by simply creating the physical relationship the magic was going to happen by itself :).

[#7]

@bartvana 

That's in Excel. In Power BI Desktop you may define that in properties. Another story is that such kind of relationships are not good from performance point of view, better to avoid where possible.