Facing issue while using Switch and IF function

[Anonymous]

Hi all i have created a calculated column using Switch function as below

Previous Weeks =

SWITCH (

TRUE (),

Dim_MasterCalendar[Weeks]>=-3 && Dim_MasterCalendar[Weeks]<0, "Last 3 Weeks",

Dim_MasterCalendar[Weeks]>=-13 && Dim_MasterCalendar[Weeks]<0, "Last 13 Weeks",

Dim_MasterCalendar[Weeks]>=-52 && Dim_MasterCalendar[Weeks]<0, "Last 52 Weeks","Unkown")

If you see the result when i filter 'Last 3 weeks' its showing correct data but when i select the 'last 13 weeks' its eliminating the rows which falls under 'last 3 weeks' same issue when i select 'last 52 weeks' its eliminating both 'last 3 weeks' and 'last 13 weeks' rows.

Same calculate column i have created using IF function then also i am facing the same issue.

so my requirement is to get all the 13 rows when i select 'last 13 weeks' please help me on this issue.

[v-juanli-msft]

Hi @Anonymous 

As AlB said, Another option would be to work with a measure applied to a visual filter,

I make a test as below

Create a new table(table(2)), no relationship with other table

Create measures in date table(table1)

last weeks = DATEDIFF(MAX('Table'[Date]),TODAY(),WEEK)

Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Table (2)'[slicer] ) = "last 3 weeks", IF ( [last weeks] > 0 && [last weeks] <= 3, 1 ),
    SELECTEDVALUE ( 'Table (2)'[slicer] ) = "last 13 weeks", IF ( [last weeks] > 0 && [last weeks] <= 13, 1 ),
    SELECTEDVALUE ( 'Table (2)'[slicer] ) = "last 52 weeks", IF ( [last weeks] > 0 && [last weeks] <= 52, 1 ),
    SELECTEDVALUE ( 'Table (2)'[slicer] ) = "unknown", IF ( [last weeks] <= 0 || [last weeks] > 52, 1 )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-juanli-msft Appriciate your work.This works fine when you create Last week measure using Today().

In my requirement i am taking date difference between my fiscal year date and auto calander date like below

Last Weeks = DATEDIFF(max(Fiscal_Date[Date]),Dim_MasterCalendar[Date],WEEK) ---This can't be created using a measure this is a calculated column.

In my fiscal date i have data up to June month only. So when i create a visual and a slicer for last 4 weeks it should show last 4 weeks from June not from the current month. Please help me if you have any solution for this.

[AnthonyTilley]

in your example you will always lose the other options becasue what you have done is to create a colunm of text values that have no relevence to one another 

it would be no diffrent to saying if less than 3 then "A"

less than 13 then "B" 

and less tan 52 then "C" 

A,B and C in a slicer whould not create any kind of grouping where by selecting A also selects C which is what you are trying to do 

i would guess that what you are trying to achive is that you only ever look back over 3, 13 and 52 weeks .

you have joind this to a date table where you have a calculated colunm that determins the week position in relation to the current week no. so if it is week 10 then week 9 would be -1 and week 8 would be -2 etc 

so what you are trying to do is to say if i select 3 weeks then i want every thing between 0 and -3 when i select 13 weeks i want everything between 0 and -13 etc but in your example selecting 13 weeks cannot select 3 weeks as well as these have a diffrent value. 

i can think of a way to achive this but it can be a bit long winded and needs to be applied to every measure you use 

best option is to stick with week no in a slicer and just have the user toggle, or tell the user they have to select all required 

[AlB]

Hi @Anonymous 

You'd need to change the logic. You are placing the different values in bins that are mutually exclusive when you need something with an overlap. Rows that are "active" in last three weeks should be active in last 13 and last 52 as well.  One quick solution is change the names to something like

between 0 and 3

between 4 and 13, etc...

which is what your code is actually doing. You can then select  both between 0 and 3 and  between 4 and 13 for the last 13.

Another option would be to work with a measure applied to a visual filter.

[PattemManohar]

@Anonymous As your last three weeks fall into multiple category i.e "Last 3 Weeks" as well as "Last 13 Weeks". So based on the order of the conditions in your switch the first condition it fall under will get that value assigned  (in this case "Last 3 Weeks" will get assigned) and for the same weeks/dates you can't have another value assigned (as you are expecting "Last 13 Weeks" for the same ).

There are two possible ways you can get those three weeks as per your logic.

1. Select both "Last 3 Weeks" and "Last 13 Weeks" in your slicer

2. Or change the order of your switch statement, move your Last13Weeks statement to first (but doing so, you can't have Last 3 Weeks any more)

[Anonymous]

I hope i cannot implement it in one calculated column. currently i am using 3 calculated columns for three different partitions.

Thanks for your suggestion.