Forum Discussion

hpatel247's avatar
hpatel247
Icon for Helper I rankHelper I
8 years ago

Calculate with multiple filters using if/or statements

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

Need Help
Tips and Tricks

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      hpatel247
      Icon for Helper I rankHelper I
      8 years ago

      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