cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Calculated Column based on multiple filter criteria

Hello,

How to create a calculated column flagging whether or not a particular calendar Week is shared between two calendar months or not (end of month scenario), without considering Saturdays and Sundays.

I want to try with approach of distinct count of Month numbers for a Week - If it is 2, then YES, else NO.

Example:
Week 13 is shared between March and April discounting Sat and Sun, thus Yes is needed.
Week 17 is Not shared between April and May discounting Sat and Sun, thus No is needed.
Table Dim_Date

 Date Week Number Month Number Weekday Calculate_Week shared between months? 04-Apr-21 13 4 Sun Yes 03-Apr-21 13 4 Sat Yes 02-Apr-21 13 4 Fri Yes 01-Apr-21 13 4 Thu Yes 31-Mar-21 13 3 Wed Yes 30-Mar-21 13 3 Tue Yes 29-Mar-21 13 3 Mon Yes 02-May-21 17 5 Sun No 01-May-21 17 5 Sat No 30-Apr-21 17 4 Fri No 29-Apr-21 17 4 Thu No 28-Apr-21 17 4 Wed No 27-Apr-21 17 4 Tue No 26-Apr-21 17 4 Mon No

I tried below code but it resulted into "No" for Week 13 above too. It does look fishy to me but unable to fix this. Any help would be great.

``````Calculate_Week shared between months? =
IF(
CALCULATE(
CALCULATE(DISTINCTCOUNT(Dim_Date[Month Number]),
ALLEXCEPT(Dim_Date,Dim_Date[Week Number])),
FILTER(Dim_Date,
OR( Dim_Date[Weekday] <> "Sun", Dim_Date[Weekday] <> "Sat"))
)
= 2, "Yes", "No"
)``````

Thanks

Nirmit

1 ACCEPTED SOLUTION
Super User

@Anonymous ,

Var _week = [Week]
return
if(calculate(distinctCOUNT(Dim_Date[Month Number]), filter(Dim_Date, Dim_Date[Week] =_week && not( Dim_Date[Weekday] in{"Sun", "Sat"}))) =2, "Yes", "No")

4 REPLIES 4
Anonymous
Not applicable

Many thanks for taking time out to explain this with example. Indeed it should be the inner ALLEXCEPT() removing all other context filters. Though I do need to understand these nuances in more detail!

Regards

Nirmit

Community Support

Hi @Anonymous ,

Based on my test, if you use the following formula to modify your second method, the result is correct.

``````Column =
VAR _week = [Week Number]

RETURN
IF (
CALCULATE (
CALCULATE (
DISTINCTCOUNT ( Dim_Date[Month Number] ),
FILTER ( 'Dim_Date', 'Dim_Date'[Week Number] = _week )
),
FILTER ( Dim_Date, NOT ( Dim_Date[Weekday] IN { "Sun", "Sat" } ) )
) = 2,
"Yes",
"No"
)``````

So I think it could be related to the context with ALLEXCEPT() —— The two groups of expression consider or ignore the filters made on related tables, respectively.As the official document said, ALLEXCEPT() removes all context filters in the table except filters that have been applied to the specified columns.

https://community.powerbi.com/t5/Desktop/Filter-and-Allexcept/td-p/76403

https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/

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

Anonymous
Not applicable

Thanks much for quick help. Really appreciate. It works exactly like required.

I used the FILTER syntax you provided and sourrounded it with ALLEXCEPT- this works
But ALLEXCEPT sourrounded by FILTER - this does not work

Would be very helpful if you could explain a bit on this. Thanks in advance.

This works:

``````Week shared between months =
IF(
CALCULATE(
CALCULATE
(
DISTINCTCOUNT(Dim_Date[Month Number]),
FILTER(Dim_Date, NOT (Dim_Date[Weekday] IN {"Sun", "Sat"}))
),
ALLEXCEPT(Dim_Date,Dim_Date[Week])
)
= 2, "Yes", "No"
)``````

This does NOT work. (Result of IF is 12 always)

``````IF(
CALCULATE(
CALCULATE
(
DISTINCTCOUNT(Dim_Date[Month Number]),
ALLEXCEPT(Dim_Date,Dim_Date[Week])
),
FILTER(Dim_Date, NOT (Dim_Date[Weekday] IN {"Sun", "Sat"}))
)
= 2, "Yes", "No"
)``````

Thanks

Nirmit

Super User

@Anonymous ,

Var _week = [Week]
return
if(calculate(distinctCOUNT(Dim_Date[Month Number]), filter(Dim_Date, Dim_Date[Week] =_week && not( Dim_Date[Weekday] in{"Sun", "Sat"}))) =2, "Yes", "No")

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors