How do I show 2 columns in a query table in a text box instead of the table visual?

[EaglesTony]

Hi,

  I have the following in a table:

Green  50

Yellow  20

Red      100

  I don't want to show this on the report as a table, but as text as such:

"Total Green: 50"

"Total Yellow: 20"

"Total Red: 100"

Thanks

[EaglesTony]

Hi,

  I tried your suggestion, but getting a syntax error "The syntax for "Total" is incorrect.

  Here is what I have:

Measure = var a="Green" var b="Yellow" var c="Red" var _avalue =LOOKUPVALUE(Append1[AgingDaysColor],Append1[Count],a) var _bvalue=LOOKUPVALUE(Append1[AgingDaysColor],Append1[Count],b) var _cvalue=LOOKUPVALUE(Append1[AgingDaysColor],Append1[Count],c) "Total"&" "&a&":"&" "&_avalue&UNICHAR(10)&

"Total"&" "&b&":"&" "&_bvalue&UNICHAR(10)&

"Total"&" "&c&":"&" "&_cvalue

[v-xinruzhu-msft]

Hi @EaglesTony 

You need to add "return ", you can refer to the following measure

Measure = var a="Green" 
var b="Yellow"
 var c="Red" 
var _avalue =LOOKUPVALUE(Append1[AgingDaysColor],Append1[Count],a) 
var _bvalue=LOOKUPVALUE(Append1[AgingDaysColor],Append1[Count],b) 
var _cvalue=LOOKUPVALUE(Append1[AgingDaysColor],Append1[Count],c) 
return "Total"&" "&a&":"&" "&_avalue&UNICHAR(10)&
"Total"&" "&b&":"&" "&_bvalue&UNICHAR(10)&
"Total"&" "&c&":"&" "&_cvalue

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EaglesTony]

I tried this but when I put the field to the card, I am getting an error:

AgingDaysColor is Text, Count is a Whole Number

[EaglesTony · Accepted solution]

The issue was the columns needed flipped around.

Measure = var a="Green" 
var b="Yellow"
 var c="Red" 
var _avalue =LOOKUPVALUE(Append1[Count],Append1[AgingDaysColor],a) 
var _bvalue=LOOKUPVALUE(Append1[Count],Append1[AgingDaysColor],b) 
var _cvalue=LOOKUPVALUE(Append1[Count],Append1[AgingDaysColor],c) 
return "Total"&" "&a&":"&" "&_avalue&UNICHAR(10)&
"Total"&" "&b&":"&" "&_bvalue&UNICHAR(10)&
"Total"&" "&c&":"&" "&_cvalue

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[v-xinruzhu-msft]

Hi @EaglesTony 

Based on the sample data you provide, you can refer to the following measures (if there is less data).

Measure = var a="Green"
var b="Yellow"
var c="Red"
var _avalue=LOOKUPVALUE('Table'[Column2],'Table'[Column1],a)
var _bvalue=LOOKUPVALUE('Table'[Column2],'Table'[Column1],b)
var _cvalue=LOOKUPVALUE('Table'[Column2],'Table'[Column1],c)
return "Total"&" "&a&":"&" "&_avalue&UNICHAR(10)&
"Total"&" "&b&":"&" "&_bvalue&UNICHAR(10)&
"Total"&" "&c&":"&" "&_cvalue

Then put the measure to the card visual

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.