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_KeyProduct_Type
P1Handset
P2Handset
P3FBB
P4Handset

 

Device 
Device_KeyDevice_Name
D1iPhone 16
D2Samsung S Pro
D3Nokia

 

Sales    
DateIDProduct_KeyDevice_KeyConnection
28/05/20251P1D11
28/05/20252P1D11
28/05/20253P1D11
28/05/20254P2D21
28/05/20255P3D21
28/05/20256P3D31

 

I need the output as below

TypeConnections
Apple3
Samsung1
FBB1
Handset1

 

where logic for Type field is to be calculated as (this is Qliksense logic)

=If(WildMatch(Device_Name,'*IPHONE*')=1,'Apple',
If(WildMatch(Device_Name,'*SAM *')=1 And [Product_Type]='Handset','Samsung',
If(Product_Type='MBB','ISP/MBB',
            If(Product_Type='FBB','FBB',
                If(Product_Type='Wearable','Wearable','Other Handsets')))))
  • 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"
)

9 Replies

  • 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



  • sandygather's avatar
    sandygather
    Frequent Visitor
    1 year ago

    Thanks Samson!

     

    Below is the actual Data Model

    I get the below error while defining the column 'Type'

     

    • 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?