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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.