Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 Su...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

 

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.