Feb 11 2022 03:07 AM - edited Feb 11 2022 03:08 AM
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...
Feb 11 2022 04:10 AM
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]
)
)
Feb 11 2022 04:11 AM
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
Feb 12 2022 01:04 AM - edited Feb 12 2022 01:07 AM
@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?
Feb 12 2022 02:05 AM
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
Feb 12 2022 04:41 AM
@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 :).
Feb 12 2022 05:50 AM
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.
Feb 11 2022 04:10 AM
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]
)
)