Need Help - DAX code

[sandygather]

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	Handset
P2	Handset
P3	FBB
P4	Handset

Device
Device_Key	Device_Name
D1	iPhone 16
D2	Samsung S Pro
D3	Nokia

Sales
Date	ID	Product_Key	Device_Key	Connection
28/05/2025	1	P1	D1	1
28/05/2025	2	P1	D1	1
28/05/2025	3	P1	D1	1
28/05/2025	4	P2	D2	1
28/05/2025	5	P3	D2	1
28/05/2025	6	P3	D3	1

I need the output as below

Type	Connections
Apple	3
Samsung	1
FBB	1
Handset	1

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')))))

[sandygather]

Thanks Samson!

Below is the actual Data Model

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

[SamsonTruong]

Hi @sandygather ,

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

[sandygather]

Hi @SamsonTruong

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

[SamsonTruong]

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

[sandygather]

@SamsonTruong Please see below

[SamsonTruong · Accepted solution]

@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"
)

[sandygather]

@SamsonTruong 

Thanks!

Works perfectly.

[SamsonTruong]

@sandygather Awesome, glad to hear it worked!

[SamsonTruong]

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