topic Re: Need Help - DAX code in DAX Commands and Tips

Need Help - DAX code

sandygather — Wed, 28 May 2025 02:53:37 GMT

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

Re: Need Help - DAX code

SamsonTruong — Wed, 28 May 2025 22:42:05 GMT

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

Re: Need Help - DAX code

sandygather — Wed, 28 May 2025 23:02:29 GMT

Thanks Samson!

Below is the actual Data Model

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

Re: Need Help - DAX code

SamsonTruong — Wed, 28 May 2025 23:08:27 GMT

Hi sandygather ,

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

Re: Need Help - DAX code

sandygather — Wed, 28 May 2025 23:12:33 GMT

Hi SamsonTruong

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

Re: Need Help - DAX code

SamsonTruong — Wed, 28 May 2025 23:17:26 GMT

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

Re: Need Help - DAX code

sandygather — Thu, 29 May 2025 00:04:19 GMT

SamsonTruong Please see below

Re: Need Help - DAX code

SamsonTruong — Thu, 29 May 2025 00:27:01 GMT

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

Re: Need Help - DAX code

sandygather — Thu, 29 May 2025 03:18:08 GMT

SamsonTruong

Thanks!

Works perfectly.

Re: Need Help - DAX code

SamsonTruong — Thu, 29 May 2025 04:38:01 GMT

sandygather Awesome, glad to hear it worked!