I'm getting an error A single value for column 'Department' in table 'DIA SALES DATA' cannot be dete

[pagarc1]

Order Type = if('DIA SALES DATA'[Department]="24HRTS","DTP Nonrevenue",IF('DIA SALES DATA'[Department]="Distributor","Distributor","DTP Revenue"))

error below

A single value for column 'Department' in table 'DIA SALES DATA' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[az38]

Hi @pagarc1 

as you use a measure the DAX cannot understand what exactly cell of Department column you want to use for comparing.

So try to use SELECTEDVALUE() if you are trying to compare value in each rows, like

Order Type = if(SELECTEDVALUE('DIA SALES DATA'[Department])="24HRTS","DTP Nonrevenue",IF(SELECTEDVALUE('DIA SALES DATA'[Department])="Distributor","Distributor","DTP Revenue"))

or better use SWITCH() statement

SWITCH(
SELECTEDVALUE('DIA SALES DATA'[Department]),
"24HRTS","DTP Nonrevenue",
"Distributor","Distributor",
"DTP Revenue"
)

[Anonymous]

Hi @pagarc1 ,

In measure you can not refer column directly.

Your dax will work for calculated column not for measure.

Incase you want to do the calculation using measure add 'min'/'max'/Selectevalue before column name

Order Type = if(max('DIA SALES DATA'[Department])="24HRTS","DTP Nonrevenue",IF(max('DIA SALES DATA'[Department])="Distributor","Distributor","DTP Revenue"))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

You can add only columns as slicer.

Create calculated column as below

Order Type = if('DIA SALES DATA'[Department]="24HRTS","DTP Nonrevenue",IF('DIA SALES DATA'[Department]="Distributor","Distributor","DTP Revenue"))

Note:

Measures are used for aggrigation and dynamic calculations.

Measure can be used as visual level filters but measures can be used in slicers.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[pagarc1]

I want to do something like this. I want to add it on a slicer although the previous formula worked. I couldn't add the field into a slicer.

[Chthonian]

Hi @pagarc1 ,

While I cannot definitively answer why you are getting this without seeing your Data Model, in my experience when you get this type of error it is generally down to the data model relationships and setup. 

A good resource on this:

DAX error messages in Power BI