How to get a list of values from a column in DAX?

[YukiK]

I'd like to get a list of values of a column in a table so that I can do a condition where " if (_myDepartment in _valuesOfColumn, do A, else B)". I'll use the boolean value from that variable in another variable.

I can use Values function, but I need to ignore the context filter so the list has the full list of values.

In short:

I currently have a variable where

VAR _listOfdistinctPrevUnits = { "E3", "L4", "F3", "B2", "B3", "L6" }

This essentially the values from a column of a table. I guess I wanna get these values dynamically.

I can't seem to find an answer for this, but any idea for how to accomplish this?

[AlexisOlson · Accepted solution]

The VALUES function does this. Consider other similar functions too depending on your exact requirements: DISTINCT, ALL, ALLSELECTED).

[v-shex-msft · Accepted solution]

HI @YukiK,

You can try to use the following measure formula to compare selection and list of values to return tag:

Measure =
IF (
    COUNTROWS ( INTERSECT ( VALUES ( Table[Column] ), ALL ( Tableb[Column] ) ) ) > 0,
    "A",
    "B"
)

Regards,

Xiaoxin Sheng

[AlexisOlson · Accepted solution]

The VALUES function does this. Consider other similar functions too depending on your exact requirements: DISTINCT, ALL, ALLSELECTED).

[amitchandak]

@YukiK , assume you want to use in a filter

filter(Table, _myDepartment in values(Table[Column]))

or

filter(Table, _myDepartment in allselected(Table[Column]))