cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirmit27
Helper II
Helper II

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

DateWeek NumberMonth NumberWeekdayCalculate_Week shared between months?
04-Apr-21134SunYes
03-Apr-21134SatYes
02-Apr-21134FriYes
01-Apr-21134ThuYes
31-Mar-21133WedYes
30-Mar-21133TueYes
29-Mar-21133MonYes
     
02-May-21175SunNo
01-May-21175SatNo
30-Apr-21174FriNo
29-Apr-21174ThuNo
28-Apr-21174WedNo
27-Apr-21174TueNo
26-Apr-21174MonNo

 

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
amitchandak
Super User
Super User

@nirmit27 ,


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")

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

View solution in original post

4 REPLIES 4
nirmit27
Helper II
Helper II

Hi @Eyelyn9 

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

Eyelyn9
Community Support
Community Support

Hi @nirmit27 ,

 

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"
    )

 

 

Calculated Column based on multiple filter criteria.PNG

 

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.

 

For more details, please refer to thread:

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.

nirmit27
Helper II
Helper II

Hi @amitchandak 

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

amitchandak
Super User
Super User

@nirmit27 ,


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")

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!