Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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
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
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |