If text column = x then decimal measure = y

[Qotsa]

Hi,

I have a text col 'Custom' & a decimal measure 'Last 8 Wks Offered'

What I'm trying to return is that if 'Custom' = "Carer" then 'Last 8 Wks Offered' = "N/A" else 'Last 8 Weeks Offered"

Test = IF(Append1[Custom] = "Carer",FORMAT([Last 8 Wks Offered],BLANK(),"en-GB"),[Last 8 Wks Offered])

The above returns the error 'Expressions that yield variant data-type cannot be used to define calculated columns.'

[Fowmy · Accepted solution]

@Qotsa 

It's not possible to have different data-types in one column, you change the else part to text by modifying your formula as follows:

Test =
IF (
    Append1[Custom] = "Carer",
    FORMAT ( [Last 8 Wks Offered], BLANK (), "en-GB" ),
    FORMAT ( [Last 8 Wks Offered] , "#,0.00")
)

[Qotsa]

@PC2790  That does it. TY.

[PC2790 · Accepted solution]

You can do something within the measure- [Last 8 Wks Offered] like:

DAX Measure =

var selectedcustom = SelectedValue(Append1[Custom] )

return
IF(selectedcustom = "Carer",BLANK(),<logic to get [Last 8 Wks Offered]>)

[Fowmy · Accepted solution]

@Qotsa 

It's not possible to have different data-types in one column, you change the else part to text by modifying your formula as follows:

Test =
IF (
    Append1[Custom] = "Carer",
    FORMAT ( [Last 8 Wks Offered], BLANK (), "en-GB" ),
    FORMAT ( [Last 8 Wks Offered] , "#,0.00")
)