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...
Need Help
Tips and Tricks
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