Forum Discussion

sandygather's avatar
sandygather
Frequent Visitor
1 year ago
Solved

Need Help - DAX code

Hi All, I am new to Power BI and need help in writing a DAX expression   I have 2 Dimension tables Product and Device and 1 fact table Sales. Product   Product_Key Product_Type P1 Hand...
  • SamsonTruong's avatar
    SamsonTruong
    1 year ago

    sandygather Thank you for providing these screenshots. Alternatively, we can try to use a lookup instead of the related function. Please let me know if the DAX below works for the 'Type' column:

    Type =
VAR DeviceLongName = LOOKUPVALUE(
    'Provisioned Device'[Provisioned_Dvc_Long_Nm],
    'Provisioned Device'[Provisioned_Dvc_Prod_Key],
    'Base Movement'[Dvc_Prod_Key]
)

VAR ProductGroup = LOOKUPVALUE(
    'Provisioned Device'[Provisioned_Dvc_Product_Group_1],
    'Provisioned Device'[Provisioned_Dvc_Prod_Key],
    'Base Movement'[Dvc_Prod_Key]
)

VAR PlanType = LOOKUPVALUE(
    'Product'[plan_type_cd],
    'Product'[Prod_Key],
    'Base Movement'[Prim_Prod_Key]
)

RETURN
SWITCH(
    TRUE(),
    SEARCH("IPHONE", UPPER(DeviceLongName), 1, 0) > 0, "iPhone",
    SEARCH("SAM", UPPER(ProductGroup), 1, 0) > 0 && PlanType = "Handset", "Samsung",
    PlanType = "MBB", "ISP/MBB",
    PlanType = "FBB", "FBB",
    PlanType = "Wearable", "Wearable",
    "Other Handsets"
)
SamsonTruong's avatar
SamsonTruong
Super User
1 year ago

Hi sandygather ,

Thank you for the additional context. Is the 'Type' column being created in the 'Base Movem..' Fact Table?

sandygather's avatar
sandygather
Frequent Visitor
1 year ago

Hi SamsonTruong

 

Yes that is correct. It is the Fact table (Sales as per my original post)

  • SamsonTruong's avatar
    SamsonTruong
    Super User
    1 year ago

    sandygather  By any chance, would you be able to send a screenshot of the relationships in your model?

      • SamsonTruong's avatar
        SamsonTruong
        Super User
        1 year ago

        sandygather Thank you for providing these screenshots. Alternatively, we can try to use a lookup instead of the related function. Please let me know if the DAX below works for the 'Type' column:

        Type =
VAR DeviceLongName = LOOKUPVALUE(
    'Provisioned Device'[Provisioned_Dvc_Long_Nm],
    'Provisioned Device'[Provisioned_Dvc_Prod_Key],
    'Base Movement'[Dvc_Prod_Key]
)

VAR ProductGroup = LOOKUPVALUE(
    'Provisioned Device'[Provisioned_Dvc_Product_Group_1],
    'Provisioned Device'[Provisioned_Dvc_Prod_Key],
    'Base Movement'[Dvc_Prod_Key]
)

VAR PlanType = LOOKUPVALUE(
    'Product'[plan_type_cd],
    'Product'[Prod_Key],
    'Base Movement'[Prim_Prod_Key]
)

RETURN
SWITCH(
    TRUE(),
    SEARCH("IPHONE", UPPER(DeviceLongName), 1, 0) > 0, "iPhone",
    SEARCH("SAM", UPPER(ProductGroup), 1, 0) > 0 && PlanType = "Handset", "Samsung",
    PlanType = "MBB", "ISP/MBB",
    PlanType = "FBB", "FBB",
    PlanType = "Wearable", "Wearable",
    "Other Handsets"
)