Switch function with If statement

[MilindG]

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

I have been stuck here for couple of days so any help would be greatly appreciated.

Thank you.

[Anonymous · Accepted solution]

Hi @MilindG ,

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 Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MilindG]

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

[lbendlin]

You lost me a bit here. Are you trying to apply multiple rules at the same time?

[lbendlin]

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.