Forum Discussion
Power Pivot relations
bartvana All credit should go to SergeiBaklan , 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] ) )
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
SergeiBaklan 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 https://docs.microsoft.com/en-us/dax/treatas-function 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?
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
SergeiBaklan 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 :).
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.