Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
sandygather
Frequent Visitor

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

View solution in original post

9 REPLIES 9
sandygather
Frequent Visitor

Thanks Samson!

 

Below is the actual Data Model

sandygather_0-1748473288374.png

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

sandygather_1-1748473332307.png

 

Hi @sandygather ,

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

Hi @SamsonTruong

 

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

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

@SamsonTruong Please see below

sandygather_0-1748477038836.pngsandygather_1-1748477052774.png

 

@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 

Thanks!

Works perfectly.

@sandygather Awesome, glad to hear it worked!

SamsonTruong
Solution Supplier
Solution Supplier

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:

SamsonTruong_0-1748471698311.png



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:

SamsonTruong_1-1748471993850.png

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



Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.