Measure returns the wrong value when used in a text box but the correct value in other visuals

[calumt]

Hello, 

I've been working on creating a measure for a report which will look at the numerator and denominator size for a given outcome. The measure should return a "..." if the denominator is <=30 persons, or a "*" if the numerator is <=5 and >0 persons. If these conditions are not met, the measure should return a blank value. 

The issue I've run into is the measure returns "..." no matter what when I use it in a text box. However, if I use this measure in a table or card visual it returns the correct value. I've included a screenshot of the issue. 

Any help is greatly appreciated!

The code for the measure is: 

Suppression Measure = var denom = LOOKUPVALUE(Table[denom],Table[Date],[selected_date])

var num = LOOKUPVALUE(Table[num],Table[Date],[selected_date])

RETURN

SWITCH(TRUE(),denom<=30,"..",num<=5 && num>0,"*"," ")

[Ashish_Mathur]

Hi,

What exactly are you trying to do?  Share some data, explain the question and show the expected result.  Also, why are you showing the result of a measure in a text box?  Why not in a card visual? 

[calumt]

Hi,

I'm trying to write a measure that looks at the numerator/denominator value and sets a symbol for users to know if the denominator is small (n<30), or if the numerator is between 1-5. If neither condition is met it should return a blank value. The reason I'm using a text box vs a card is because I need to include some written text along with the answer. Its easiest to do this in a textbox as I can't format the text the same using a card visual.

I'm not able to share direct data due to privacy concerns, but I've included a screenshot with the raw data %, numerator and denominator; the suppression measure; and the data by date. This is currently working correctly, in October of 2018 there were >30 persons in the denominator and >5 in the numerator. In October 2019, there were <30 in the denominator. Thus, when selecting October 2019, the suppression measure should show "..". 

This works fine in a table, but the second I use a textbox measure I am either unable to add the named measure as a value, or the measure returns the wrong value.

Sample Data:

Date	%	Num	Denom	Expected Result
Oct 2018	61.5%	24	39
Oct 2019	53.3%	8	15	..

[lbendlin]

Measures are computed multiple times in a visual, depending on context. For example in a table visual the measure is computed for each row, and again one time for the column totals.  In a text visual the measure is calculated for the totals (all items in your filter context).  So things like "SELECTEDVALUE()"  are meaningless for some visuals.

[calumt]

Hello,

Is this different from how it will be calculated when using a measure in a textbox? The measure works perfectly as a card or table visual, but not in a textbox. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[calumt]

Apologies, here is the sample data:

Date	%	Num	Denom	Expected Result
Oct 2018	61.5%	24	39
Oct 2019	53.3%	8	15	..

[lbendlin]

seems to work fine?

[calumt]

Weird, I still have the issue where if I call a measure it returns the wrong result or shows a red underline. 

[lbendlin]

are you sure that is a measure and not a calculated column?

[calumt · Accepted solution]

100% sure

Edit: Ended up having to change to a card visual