Dax If statement. I am trying to convert a formular from Tableau to Power BI.

[Anonymous]

Tableau formular:

IF [Unit Nbr] = [Cost Ctr Nbr] then 'DRIVE Unit' else 'Non DRIVE' END

Power Bi Dax formular: 

Drive Unit = If('AP Paid unit detail'[UNIT_NBR]='Drive_In_Scope_Unit_List'[Cost Ctr Nbr], "Drive Unit", "Non Drive Unit")

The problem is " A single value for column 'Cost Ctr Nbr' in table 'Drive_In_Scope_Unit_List' 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....."

How can i fix this? 

Thank you so much 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Assume you have tables

You could create a column/ measure in "AP Paid unit detail" table

Column =
VAR lookvalue =
    LOOKUPVALUE (
        Drive_In_Scope_Unit_List[Cost Ctr Nbr],
        Drive_In_Scope_Unit_List[Cost Ctr Nbr], 'AP Paid unit detail'[UNIT_NBR]
    )
RETURN
    IF ( lookvalue = BLANK (), "Non DRIVE", "DRIVE Unit" )

Measure =
VAR lookvalue =
    LOOKUPVALUE (
        Drive_In_Scope_Unit_List[Cost Ctr Nbr],
        Drive_In_Scope_Unit_List[Cost Ctr Nbr], MAX ( 'AP Paid unit detail'[UNIT_NBR] )
    )
RETURN
    IF ( lookvalue = BLANK (), "Non DRIVE", "DRIVE Unit" )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tahreem24]

Try to use below measure:
Drive Unit = If(Sum('AP Paid unit detail'[UNIT_NBR]) =Sum('Drive_In_Scope_Unit_List'[Cost Ctr Nbr]) , "Drive Unit", "Non Drive Unit")

Please hit Thumbs up and mark it as a solution if it helps you.

[az38]

Hi @Anonymous 

it looks like are trying to create measure, but you need a calculated column

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

So how can i do that?