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,

I was able to replicate the logic in your expression with the following DAX column for 'Type':

Type = 
VAR DeviceName = RELATED(Device[Device_Name])
VAR ProductType = RELATED(Product[Product_Type])
RETURN
    SWITCH(
        TRUE(),
        SEARCH("IPHONE", UPPER(DeviceName), 1, 0) > 0, "Apple",
        SEARCH("SAM", UPPER(DeviceName), 1, 0) > 0 && ProductType = "Handset", "Samsung",
        ProductType = "MBB", "ISP/MBB",
        ProductType = "FBB", "FBB",
        ProductType = "Wearable", "Wearable",
        "Other Handsets"
    )

 

First I added this calculated DAX column to the Sales fact table which gave me the following output:



I then created the following DAX measure in the Sales fact table to calcualte the total connections for each type:

Total Connections = 
SUM(Sales[Connection])

 
I then placed the newly created column for 'Type' and the newly created measure for 'Total Connections' in a table to get the following output:

As a note, based on your provided logic and inputs, there is not a condition where the output would be 'Handset' and there are two records where the condition for 'FBB' is met (lines 5 and 6). Please let me know if there is potentially a missing condition.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson