Trying to create a measure using switch

[Anonymous]

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.7

 Thank you!

[harshnathani]

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!!

[harshnathani]

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!!

[Anonymous]

Hi and thanks for your help,

However it appears that the RELATED parameter is not the correct type.

Thanks

[az38 · Accepted solution]

@Anonymous 

create column, not a measure.

make sure you have relationships between tables

[az38]

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
)

[amitchandak]

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

[Anonymous]

Hi,

I forgot to mention that the SalesGroup data exists in a different table (SalesGroup) in the model.

Can it still be achieved using SWITCH?

Thank you.