Forum Discussion
Switch function with If statement
Hello community,
I need your help with rewriting switch function with If statement. Below is my current code
Coke Goal =
VAR _7D=MAX(table 1[DATE])-7
VAR _30D=MAX(table 1[DATE])-30
VAR _365D=MAX(table 1[DATE])-365
VAR _switch=
SWITCH(
TRUE(),
MAX('Table'[Value])="7D",7,
MAX('Table'[Value])="30D",30,
MAX('Table'[Value])="30D",365)
RETURN
CALCULATE(
SUM(Table 2[Coke_Goal]) * _switch
)
What the code is doing is basically multiplying the "Coke goal" values to 7, 30 and 365.
I have table 3 which shows site number and how many days it has been opened for.
| Site | Days Open |
| 1 | 35 |
| 2 | 200 |
| 3 | 15 |
| 4 | 400 |
What I want to do is for the 7 multiplication, if the site has been open more than 7 days, then multiply value by 7, for the 30, if the site is open more than 30 days, multiple value by 30 and for 365, multiple vfalue by 365 else multilple value by the days it has been open if any condition does not match. Please see belows post for your reference.
https://community.fabric.microsoft.com/t5/Desktop/Formatting-dashboard-with-multiple-filters/m-p/4234349#M1330962
I have been stuck here for couple of days so any help would be greatly appreciated.
Thank you.
Hi Anonymous ,
Maybe you can try formula like below to create calculated column:
Coke Value = SWITCH ( TRUE (), 'Table'[Days Open] < 365, 'Table'[Days Open] * 'Table'[Coke Goal], 'Table'[Days Open] >= 365, 365 * 'Table'[Coke Goal] )Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
What I want to do is for the 7 multiplication, if the site has been open more than 7 days, then multiply value by 7, for the 30, if the site is open more than 30 days, multiple value by 30 and for 365, multiple vfalue by 365 else multilple value by the days it has been open if any condition does not match.You will not be able to distinguish between "more than 365 days" and "else" .
You will need to provide another cutoff value.
You need to switch the order of execution.
Note that you defined variables but didn't use them
Coke Goal = var md = TODAY()-max('Table 1'[Date]) var _switch = switch(TRUE(), md>730,md, md>=365,365, md>=30,30, md>=7,7) return calculate(sum('Table 2'[Coke_Goal]) * _switch)Note that this will result in BLANK if the difference is less than 7.
lbendlin Just to clarify
"You will not be able to distinguish between "more than 365 days" and "else"Site Days Open Coke Goal 1 35 1 2 200 0.5 3 15 2.5 4 400 2 Site Coke Value 1 7d 7 (7* goal) 30d 30 (30 * goal) 365d 35 ( Since days open is less than 365, I want the goal * days open) 4 7d 14 30d 60 365d 730( since the days open is over 365, I want 365 * the goal) This is what I'm trying to acheive.
You lost me a bit here. Are you trying to apply multiple rules at the same time?
Hi Anonymous ,
Maybe you can try formula like below to create calculated column:
Coke Value = SWITCH ( TRUE (), 'Table'[Days Open] < 365, 'Table'[Days Open] * 'Table'[Coke Goal], 'Table'[Days Open] >= 365, 365 * 'Table'[Coke Goal] )Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
