Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
@Anonymous
create column, not a measure.
make sure you have relationships between tables
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!!
Hi and thanks for your help,
However it appears that the RELATED parameter is not the correct type.
Thanks
@Anonymous
create column, not a measure.
make sure you have relationships between tables
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
)
@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
)
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.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
96 | |
89 | |
73 | |
61 | |
58 |