Using Switch With Calculated Variable

[Nick555]

Greetings.

I am trying to use a SWITCH statement to produce a result for a card. I want to base the result off of a percentage that is calculated. If the percentage is  >=.01 then I want the original percent that was calculated. If it is less than .01 then I want it to display "<.01". If it is blank I want it to return "Blank". Here is what I tried but it keeps giving me errors. 

new test =

    Switch(

       True(),

       divide(count(sheet1[review not completed]),count(sheet1[review date] >=.01, divide(count(sheet1[review not completed]),count(sheet1[review date],

       divide(count(sheet1[review not completed]),count(sheet1[review date] <.01, “<.01”,

       “Blank”

)

What am I doing wrong?

[tackytechtom · Accepted solution]

Hi @Nick555 ,

To me it looks like a syntax issue. Can you try this?

Measure = 
SWITCH (
    TRUE(),
    DIVIDE ( COUNT ( sheet1[review not completed] ), COUNT ( sheet1[review date] ) ) >= 0.01, DIVIDE ( COUNT ( sheet1[review not completed] ), COUNT ( sheet1[review date] ) ),
    DIVIDE ( COUNT ( sheet1[review not completed] ), COUNT ( sheet1[review date] ) ) < 0.01, "<.01",
    BLANK()
)

If this does not work, feel free to share some sample data and I can have a deeper look into it 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Nick555]

Thanks! The syntax definitely works though the result doesn’t seem to be what I was expecting. I was expecting the Pct Not Complete to show “Blank” but instead it still shows <.01%.

So I’m trying to put the dashboard below together. The bottom right card ,Pct Not Complete)  was expecting to show “blank” by using the switch coding you fixed for me. I provided some sample data in the hopes maybe we can figure this out. Any ideas will be most appreciated.

Thx

Department	DIVISION-AD	DIVISION-DD LEVEL	review date	Completed date	Review Not Completed	Completed Within 30 Days
City Manager	A	AE	March 4, 2020		1	1
City Manager	A	AE	May 16, 2020		1	1
City Manager	A	AE	July 6, 2020		1	1
City Manager	A	AE	October 6, 2020		1	1
City Manager	A	AF	January 7, 2020		1	1
City Manager	A	AF	January 15, 2020		1	1
City Manager	B	AF	September 23, 2020		1	1
City Manager	B	AG	July 7, 2020		1	1
City Manager-Arts Program	A	AG	March 27, 2020	April 6, 2020		1
City Manager-Arts Program	A	AG	May 27, 2020	May 29, 2020		1
City Manager-Arts Program	A	AH	April 30, 2020	May 15, 2020		1
City Manager-Arts Program	A	AH	December 29, 2020	January 7, 2021		1
City Manager-Arts Program	B	AH	March 11, 2020	April 2, 2020		1
City Manager-Arts Program	B	BN	September 29, 2020	October 1, 2020		1
City Manager-Arts Program	C	BN	March 9, 2020	March 24, 2020		1
City Manager-Arts Program	C	BN	June 9, 2020	June 26, 2020		1
City Manager-Arts Program	C	BN	June 26, 2020	July 8, 2020		1
Communications Office	A	MM	September 29, 2020	October 1, 2020		1
Communications Office	B	NN	September 9, 2020	September 17, 2020		1
Communications Office	B	NN	September 16, 2020	October 15, 2020		1
Communications Office	B	NN	September 28, 2020	October 1, 2020		1
Communications Office	C	LL	June 17, 2020	June 26, 2020		1
Communications Office	C	LL	November 23, 2020	December 9, 2020		1
Communications Office	D	JJ	July 17, 2020	August 5, 2020		1

[tackytechtom]

Hi @Nick555,

Hm, I can see why the measure is showing "< 0.01" cause that is how we coded it:

If your calculation returns a value equal or more than 0.01, then return that calculated value

If your calculation returns a value lowet than 0.01, then return "< 0.01"

ele return blank()

We covered all possible outcomes already in the first two if clauses, meaning we will never return blank(). Can you clarify in which cases we shall return blank()?

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Nick555]

Apologies. You are correct that we covered the outcomes. Result of staring to long at this I'm afraid.

What I was thinking/wanting was that when the card above it (Revicew Not completed count) shows Blank, that this card show blank as well.

[Nick555]

Thanks you tomfox!!

I was able to get it to work. After reviewing what you sent me, and a night of not looking at the issue, I took what you gave me and made a modification. Couldn't have done it without you!!!

[tackytechtom]

Hi @Nick555 ,

Happy, I could help you! You might wanna consider marking the reply as a solution 🙂 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/