Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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)
Solved! Go to 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"
)
Thanks Samson!
Below is the actual Data Model
I get the below error while defining the column 'Type'
Hi @sandygather ,
Thank you for the additional context. Is the 'Type' column being created in the 'Base Movem..' Fact Table?
@sandygather By any chance, would you be able to send a screenshot of the relationships in your model?
@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"
)
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
User | Count |
---|---|
16 | |
15 | |
14 | |
12 | |
11 |
User | Count |
---|---|
19 | |
15 | |
14 | |
11 | |
9 |