Forum Discussion
carguello726
7 years agoFrequent Visitor
Dimension tables ruined my data model?
Hi Power BI Pros,
I have a data model with 1 fact table and 2 lookup/dimension tables:
Material Lookup
Customer Lookup
I wish to view sales by customer and material. The pivot table works properly as shown below.
(Note - I am using Power Pivot to better illustrate my problem)
Below is the data model.
I have this DAX function (yields TRUE/FALSE) that is essentially a helper "column" for followup logical DAX functions. When I introduce this function into a pivot table or matrix, the results display what appears to be the FULL list of material items from my material lookup table. Recall that I only had 4 items for this particular customer prior to introducing this DAX function (see screenshot 1.
Here is the DAX function. It is quite long but it checks for 3 things and returns a TRUE/FALSE
Whether the item is classified as "Material" (e.g. is LookupMaterial[Material Test] = 1?)
Are sales greater than 0 in 2019 and 2018?
Is the change in average selling price (ASP) < 75%
SAME MATERIAL TEST =
VAR
IsThisMaterial = IF(CALCULATE(MIN(LookupMaterial[Material Test]))=1,TRUE(),FALSE())
VAR
SalesBothPeriod = AND([2019 Net Value II]>0, [2018 Net Value II]>0)
VAR
ASPChange = IF(OR([2019 ASP]=0,[2018 ASP] = 0), 0, ABS(DIVIDE(MAX([2019 ASP],[2018 ASP]) , MIN([2019 ASP],[2018 ASP]))-1))
RETURN
IsThisMaterial && SalesBothPeriod && ASPChange <.75
Can someone please tell me how to get the desired results below? I wish to only display the list of material items that are applicable to the customer.
I initially had this as one giant fact table and it worked just fine. This issue started when I tried to optimize the data model by using dimension tables.
Thank you in advance.
Chris
I have a data model with 1 fact table and 2 lookup/dimension tables:
Material Lookup
Customer Lookup
I wish to view sales by customer and material. The pivot table works properly as shown below.
(Note - I am using Power Pivot to better illustrate my problem)
Below is the data model.
I have this DAX function (yields TRUE/FALSE) that is essentially a helper "column" for followup logical DAX functions. When I introduce this function into a pivot table or matrix, the results display what appears to be the FULL list of material items from my material lookup table. Recall that I only had 4 items for this particular customer prior to introducing this DAX function (see screenshot 1.
Here is the DAX function. It is quite long but it checks for 3 things and returns a TRUE/FALSE
Whether the item is classified as "Material" (e.g. is LookupMaterial[Material Test] = 1?)
Are sales greater than 0 in 2019 and 2018?
Is the change in average selling price (ASP) < 75%
SAME MATERIAL TEST =
VAR
IsThisMaterial = IF(CALCULATE(MIN(LookupMaterial[Material Test]))=1,TRUE(),FALSE())
VAR
SalesBothPeriod = AND([2019 Net Value II]>0, [2018 Net Value II]>0)
VAR
ASPChange = IF(OR([2019 ASP]=0,[2018 ASP] = 0), 0, ABS(DIVIDE(MAX([2019 ASP],[2018 ASP]) , MIN([2019 ASP],[2018 ASP]))-1))
RETURN
IsThisMaterial && SalesBothPeriod && ASPChange <.75
Can someone please tell me how to get the desired results below? I wish to only display the list of material items that are applicable to the customer.
I initially had this as one giant fact table and it worked just fine. This issue started when I tried to optimize the data model by using dimension tables.
Thank you in advance.
Chris
1 Reply
- AnonymousNot applicable
HI carguello726 ,
It seems like you are trying to create a dynamic calculated column based on other table fields and lookup tables, right?
AFAIK, current power bi does not support to create dynamic calculate column/table based on slicer/filters. (measure can be affected by filter/slicers, but if you use it in calculate column, its result will be fixed as static value)BTW, if you can please remove URL transform characters from your post, it will help us to clarify your requirements and formula.
Regards,Xiaoxin Sheng