Calculate with multiple filters using if/or statements

[hpatel247]

Hi,

I am not sure if it is possible but i have 3 filter boxes with Yes/No selections. Filter boxes are:

1. Contact within last month

2. Contact within last 2 months

3. Contact within last 3 months

I have 3 columns with the above 3 options which all say Yes or No.

I want to say, if contact within last month = "Yes",Then IF(OR(contact within last 2 months="Yes",Contact within last 3 months="Yes"),"Select 1 Timescale", CALCULATE(DISTINTCOUNT('Cases'[ClientID]),'Cases'[Contact within last month="Yes")/DISTINCTCOUNT('Cases'[ClientID]) and this will continue for 2 months and 3 months.

I get a message stating "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expressions. This is not allowed"

I want to work out the % of cases where if filtered for contact within last month, it would then look at that column and count distinct values where the last month = yes divided by all cases and so on for 2 months and 3 months.

Is this possible or is there a different approach to doing this calculation?

kind regards

Hetal

[v-yulgu-msft]

Hi @hpatel247,

Maybe you can try this:

Measure =
IF (
    SELECTEDVALUE ( Selecttable1[contact within last month] ) = "Yes",
    IF (
        OR (
            SELECTEDVALUE ( Selecttable2[contact within last 2 months] ) = "Yes",
            SELECTEDVALUE ( Selecttable3[Contact within last 3 months] ) = "Yes"
        ),
        "Select 1 Timescale",
        CALCULATE (
            DISTINCTCOUNT ( 'Cases'[ClientID] ),
            FILTER ( 'Cases', 'Cases'[Contact within last month] = "Yes" )
        )
            / DISTINCTCOUNT ( 'Cases'[ClientID] )
    )
)

Best regards,

Yuliana Gu

[hpatel247]

Hi Yuliana,

Thank you for your response. I tried it the way you suggested but it didn't work.

I did manage to get a workaround this by splitting the below in 2 measures:

My Numerator is : IF([Supervision Last Month]="Yes",CALCULATE(DISTINCTCOUNT('Leaving Care'[Client Number]),FILTER('Leaving Care',[Supervision Last Month]="Yes")),IF([Supervision Last 2 Months]="Yes",CALCULATE(DISTINCTCOUNT('Leaving Care'[Client Number]),FILTER('Leaving Care',[Supervision Last 2 Months]="Yes")),IF([Supervision Last 3 Months]="Yes",CALCULATE(DISTINCTCOUNT('Leaving Care'[Client Number]),FILTER('Leaving Care',[Supervision Last 3 Months]="Yes")),CALCULATE(DISTINCTCOUNT('Leaving Care'[Client Number]),'Leaving Care'[Supervision All]="Yes"))))

My Denominator is : CALCULATE(DISTINCTCOUNT('Leaving Care'[Client Number]),ALL('Leaving Care'[Supervision in Last Month],'Leaving Care'[Supervision in last 2 Months],'Leaving Care'[Supervision in last 3 Months]))

And then the main measure which is Numerator / Denominator and it works the way i wanted it.

I appreciate your help in this matters

kind regards

Hetal