Data Modeling Problems

[carguello726]

Hi Power BI Pros,

I have a data model with 1 fact table and 2 lookup/dimension tables:

1. Material Lookup
2. 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

1. Whether the item is classified as "Material" (e.g. is LookupMaterial[Material Test] = 1?)
2. Are sales greater than 0 in 2019 and 2018
3. 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

[jdbuchanan71]

Previously, when you used the material from the single big table your measure would return blank on the rows with no matches (the materials the customer did not buy), now it is able to evaluate a FALSE so it shows the line.  Try adding a check in the return like this.

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
    IF (
        AND ( ISBLANK ( [2018 Sales] ), ISBLANK ( [2019 Sales] ) ),
        BLANK (),
        IsThisMaterial && SalesBothPeriod && ASPChange < .75
    )

[carguello726]

I tried your solution but unfortunately it produced the same result. Any other suggestions?

[jdbuchanan71]

@carguello726 Are you able to share your .pbix file?

[VasTg]

@carguello726  Please provide a mockup PBIX file to analyze.

One suggestion to test the DAX by using each variable in return function and check for expected results for each line or split a complex DAX to multiple measures, test them and combine when it works.