Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

function Values doesn t return Table of values but error

Hi,

 

I want to use VALUES function and study from https://dax.guide/values/ that input is table/column and output table (one or more columns)
I tried to od it on most simplest case but it failed, can sombebody told me why ? everything seems OK based on DAX guide, what I missed ?

 

Current Data Model

DM.png

 

My DAX formula

 

Measure = VALUES(Locations[Location])

 

DAX_values_question.png

When I tried to test it on table

Measure = VALUES(Locations)

 

I got another error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

I am totally confused, because there is only one condition that input parameter must be table or column,

any ideas what I missed ? (Don t think so that DAX.guide is wrong in description)

 

thanks in advance!

14 REPLIES 14
JamesFR06
Resolver IV
Resolver IV

 What calculation do you want to do ?

Anonymous
Not applicable

Hi @JamesFR06 ,
my whole scenario is setup 2 slicers which each one will affect second one, so if I select one speicific user in slicer, then second slicer (with location values) will be filtered based on previous option.

So my logic is following

Measure = CALCULATETABLE(VALUES(Locations), FILTER(Users,Users[Name] ))
issues is that I cannot create table that will be filtered by second parameter
then I tried to bugfix it one-by-one and see first issue in VALUES(LOCATIONS) then I am not able to display as a single measure, thefore I check documentation and see that result is tabular but error claims that it must be scalar, I dont think so that it is error in PBI and was not founded till now

affectedSlicer = Calculatetable (Cal)

It's automatic, you just have to put the column name in 1 slicer and if relationshios are ok it should be ok

Anonymous
Not applicable

unfortunatelly the relation are issue 😞 these are fix , therefore I asked here 😞
and still dont understand what VALUES return if not tabular 😞

 

Just give me an example of your tables, it would be easier for help

Anonymous
Not applicable

Hi @JamesFR06 

Here are pics,
unfortunatelly this forum doesn t support PBIX files 😞 it could be much easier
Data Model is here (unfortunatelly if both direction filter will be allowed it will works, but for this usecase I cannot swith it)

 

current status when I select random USER I see all location (instead of only them that are related to selected user)

see pict 1

1.png
0.png

so goal is to avoid these "restricted combinations"

2.png

IN your exemple, Alice had 3 Locations, LA, London and Tokyo not NY. It is normal that it gave you blank result.

So if i understood correctly you want the filter of location to be cleaned with only locations available for hte current name ?

Anonymous
Not applicable

@JamesFR06,

yes exactly, when you select user then specific location should be selected for him,
I think that it can be  achieved via userelationships but I failed

Pertr_

ok so I made an example :

JamesFR06_0-1709588309699.png

Go to "Modelisation" (Sorry I ve got pwbi in french 🙂 ) and after  on the top left relation

JamesFR06_1-1709588415610.png

You click on the name slicer

this icone will appear on location slicer. You click on it and it's done

JamesFR06_2-1709588558677.png

 

Anonymous
Not applicable

Actually, this is working only when you are filtering from fact table (In my case it is Events) I need it from dimension Users, I am aware of this option that you mentioned 🙂

ok this should work 

1 ) Measure

filter location =
var base=CALCULATETABLE(events,ALLSELECTED(User[Names]))
var loc=SELECTEDVALUE(Location[Location])
var idloc=LOOKUPVALUE(Location[IDLocation],Location[Location],loc)
var test=if(calculate(COUNTROWS(base),Events[IDLocation]=idloc)>0,1)
return
test
 
2 add this measure on the filter panel of the final filter and choose is"1"
 
It works for me
JamesFR06
Resolver IV
Resolver IV

Hi

With a measure you can return only a unique value

Values will not work because it return a list of unique values of the column.

Anonymous
Not applicable

Hello @JamesFR06 ,

thanks for input !
but in documentation is written that it returns table/tabular and not scalar, therefore I am confused

 

I also tried to use it in CALCULATE(VALUES(Locations))  and also it is not working, so I am confused again 😄

 

issue is definitively somewhere in my code

Anonymous
Not applicable

@JamesFR06I also tried CALCULATETABLE(VALUES(Locations)) and doesn t any output

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.