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've seen other posts about this error message but I'm unsure how to rectify it?
I'm trying to find out what Team an individual was in at a particular time.
Team at Position = CALCULATE (
VALUES ( 'Team Association History'[team.name] ),
FILTER (
'Team Association History',
'Cases'[ownerid] = 'Team Association History'[cr769_systemuser]
&& 'Cases'[Data Set Range] >= 'Team Association History'[Start Date]
&& 'Cases'[Data Set Range] <= 'Team Association History'[Calculated End]))
Any ideas anyone?
Thanks
Solved! Go to Solution.
@ArchStanton , You can not use values, try max, concatenatex
Team at Position = CALCULATE (
max ( 'Team Association History'[team.name] ),
FILTER (
'Team Association History',
'Cases'[ownerid] = 'Team Association History'[cr769_systemuser]
&& 'Cases'[Data Set Range] >= 'Team Association History'[Start Date]
&& 'Cases'[Data Set Range] <= 'Team Association History'[Calculated End]))
or
Team at Position = CALCULATE (
concatenateX ('Team Association History', 'Team Association History'[team.name], ", " ),
FILTER (
'Team Association History',
'Cases'[ownerid] = 'Team Association History'[cr769_systemuser]
&& 'Cases'[Data Set Range] >= 'Team Association History'[Start Date]
&& 'Cases'[Data Set Range] <= 'Team Association History'[Calculated End]))
Hi @ArchStanton ,
The error message is usually caused by 'VALUES' function, because VALUES returns all existing values in the filter context, but measures can only input a single value. When the measure returns more than one value, it gets this error.
In case of multiple values, you need to aggregate the values. You can use MAX/MIN/SUM/AVERAGE/FIRSTNONBLANK/LASNONBLANK/CONCATENATEX
If I can,I will try to make some tests later.
Good luck,
Ema
Thank you, Max instead of Values has worked, I just need to check if the results are correct
@ArchStanton , You can not use values, try max, concatenatex
Team at Position = CALCULATE (
max ( 'Team Association History'[team.name] ),
FILTER (
'Team Association History',
'Cases'[ownerid] = 'Team Association History'[cr769_systemuser]
&& 'Cases'[Data Set Range] >= 'Team Association History'[Start Date]
&& 'Cases'[Data Set Range] <= 'Team Association History'[Calculated End]))
or
Team at Position = CALCULATE (
concatenateX ('Team Association History', 'Team Association History'[team.name], ", " ),
FILTER (
'Team Association History',
'Cases'[ownerid] = 'Team Association History'[cr769_systemuser]
&& 'Cases'[Data Set Range] >= 'Team Association History'[Start Date]
&& 'Cases'[Data Set Range] <= 'Team Association History'[Calculated End]))
Many thanks - both options work although the latter brings back multiple positions for some people so I'll stick with MAX for now, I will need to check to see if the results are correct.
Grateful for your help!
User | Count |
---|---|
131 | |
71 | |
70 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |