Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
hpatel247
Helper I
Helper I

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

2 REPLIES 2
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.