function Values doesn t return Table of values but error

[Petr__]

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!

[JamesFR06]

 What calculation do you want to do ?

[Petr__]

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)

[JamesFR06]

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

[Petr__]

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

[JamesFR06]

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

[Petr__]

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"

[JamesFR06]

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 ?

[Petr__]

@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

[JamesFR06]

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

[Petr__]

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 🙂

[JamesFR06]

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]

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.

[Petr__]

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

[Petr__]

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