Forum Discussion
Trying to create a measure using switch
Hello,
I'm trying to get my head around using the SWITCH function in Power BI. However it's not working out for me so wanted to ask the community for some help.
I currently have it working in PowerQuery with a custom column with the below if, then, else statement but would like to know how to get it to work as a measure and if it would be a good use case for the SWITCH function.
if[SalesGroup] = "DISTRIBUTORS"
then ([#"Gross Sales after disc."] / 100) * 0.3
else if[SalesGroup] = "BENELUX"
then ([#"Gross Sales after disc."] / 100) * 1
else if[SalesGroup] = "FRANCE"
then ([#"Gross Sales after disc."] / 100) * 1.2
else if[SalesGroup] = "GERMANY"
then ([#"Gross Sales after disc."] / 100) * 1
else if[SalesGroup] = "IBERIA"
then ([#"Gross Sales after disc."] / 100) * 0.9
else if[SalesGroup] = "NORDIC"
then ([#"Gross Sales after disc."] / 100) * 0.6
else if[SalesGroup] = "UK"
then ([#"Gross Sales after disc."] / 100) * 0.7
else if[SalesGroup] = "DIGITAL"
then ([#"Gross Sales after disc."] / 100) * 0.7
else ([#"Gross Sales after disc."] / 100) * 10.7Thank you!
Anonymous
create column, not a measure.
make sure you have relationships between tables
7 Replies
- amitchandak
Super User
Anonymous, Assuming Switch in DAX
switch (true(),
[SalesGroup] = "DISTRIBUTORS", ([#"Gross Sales after disc."] / 100) * 0.3 ,
[SalesGroup] = "BENELUX", ([#"Gross Sales after disc."] / 100) * 1 ,
[SalesGroup] = "FRANCE" , ([#"Gross Sales after disc."] / 100) * 1.2 ,
///Other condition .....................
([#"Gross Sales after disc."] / 100) * 10.7
) - az38
Community Champion
Hi Anonymous
you don't need to create a measure, in DAX you can create a calculated column. SWITCH() is a good solution
Column = SWITCH([SalesGroup], "DISTRIBUTORS",([#"Gross Sales after disc."] / 100) * 0.3, "BENELUX", ([#"Gross Sales after disc."] / 100) * 1, "FRANCE", ([#"Gross Sales after disc."] / 100) * 1.2, "GERMANY", ([#"Gross Sales after disc."] / 100) * 1, "IBERIA", ([#"Gross Sales after disc."] / 100) * 0.9, "NORDIC", ([#"Gross Sales after disc."] / 100) * 0.6, "UK", ([#"Gross Sales after disc."] / 100) * 0.7, "DIGITAL", ([#"Gross Sales after disc."] / 100) * 0.7, ([#"Gross Sales after disc."] / 100) * 10.7 ) Hi Anonymous ,
Measure =
VAR __SalesGroup = Table[SalesGroup]
return
SWITCH(TRUE(),
(__SaleGroup = "DISTRIBUTORS" ), ([#"Gross Sales after disc."] / 100) * 0.3),
(__SaleGroup = "BENELUX" )|| (__SaleGroup = "GERMANY") ,([#"Gross Sales after disc."] / 100) * 1),
__SaleGroup = "FRANCE", [#"Gross Sales after disc."] / 100) * 1.2,
__SaleGroup = "IBERIA", [#"Gross Sales after disc."] / 100) * 0.9,
__SaleGroup = "NORDIC", [#"Gross Sales after disc."] / 100) * 0.6,__SaleGroup = "UK" || __SaleGroup = "DIGITAL, [#"Gross Sales after disc."] / 100) * 0.7,
[#"Gross Sales after disc."] / 100) * 10.7
)Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Hi Anonymous ,
You can use RELATED Function
Measure =
VAR __SalesGroup = RELATED(Table[SalesGroup])
return
SWITCH(TRUE(),
(__SaleGroup = "DISTRIBUTORS" ), ([#"Gross Sales after disc."] / 100) * 0.3),
(__SaleGroup = "BENELUX" )|| (__SaleGroup = "GERMANY") ,([#"Gross Sales after disc."] / 100) * 1),
__SaleGroup = "FRANCE", [#"Gross Sales after disc."] / 100) * 1.2,
__SaleGroup = "IBERIA", [#"Gross Sales after disc."] / 100) * 0.9,
__SaleGroup = "NORDIC", [#"Gross Sales after disc."] / 100) * 0.6,__SaleGroup = "UK" || __SaleGroup = "DIGITAL, [#"Gross Sales after disc."] / 100) * 0.7,
[#"Gross Sales after disc."] / 100) * 10.7
)If this does not work, please share some sample data and the relationship between the tables.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
