topic Re: function Values doesn t return Table of values but error in DAX Commands and Tips

function Values doesn t return Table of values but error

Anonymous — Mon, 04 Mar 2024 15:51:24 GMT

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

My DAX formula

Measure = VALUES(Locations[Location])

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!

Re: function Values doesn t return Table of values but error

JamesFR06 — Mon, 04 Mar 2024 18:06:59 GMT

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.

Re: function Values doesn t return Table of values but error

Anonymous — Mon, 04 Mar 2024 18:31:15 GMT

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

Re: function Values doesn t return Table of values but error

Anonymous — Mon, 04 Mar 2024 18:34:34 GMT

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

Re: function Values doesn t return Table of values but error

JamesFR06 — Mon, 04 Mar 2024 18:45:56 GMT

What calculation do you want to do ?

Re: function Values doesn t return Table of values but error

Anonymous — Mon, 04 Mar 2024 18:55:53 GMT

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)

Re: function Values doesn t return Table of values but error

JamesFR06 — Mon, 04 Mar 2024 20:03:45 GMT

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

Re: function Values doesn t return Table of values but error

Anonymous — Mon, 04 Mar 2024 20:44:33 GMT

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

Re: function Values doesn t return Table of values but error

JamesFR06 — Mon, 04 Mar 2024 20:55:44 GMT

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

Re: function Values doesn t return Table of values but error

Anonymous — Mon, 04 Mar 2024 21:09:57 GMT

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

so goal is to avoid these "restricted combinations"

Re: function Values doesn t return Table of values but error

JamesFR06 — Mon, 04 Mar 2024 21:32:01 GMT

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 ?

Re: function Values doesn t return Table of values but error

Anonymous — Mon, 04 Mar 2024 21:35:05 GMT

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

Re: function Values doesn t return Table of values but error

JamesFR06 — Mon, 04 Mar 2024 21:42:47 GMT

Pertr_

ok so I made an example :

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

You click on the name slicer

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

Re: function Values doesn t return Table of values but error

Anonymous — Mon, 04 Mar 2024 21:48:31 GMT

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 🙂

Re: function Values doesn t return Table of values but error

JamesFR06 — Mon, 04 Mar 2024 22:17:22 GMT

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