Cannot use my age column in if statement

[Anonymous]

Hello all,

I'm trying to create age category so I can visualise my customers based on age category. However, when I created the measure, I cannot use my age column in my if statement.

Attached below is what happened:

Also, how can I create a new column which is a concatenated value from column 1 and column 2 such that :

Column 1	Column 2	Column 3
A	B	AB
My	Address	My Address

Thank you!

Please help!

@amitchandak 

[Ashish_Mathur]

Hi,

Write this as a calculated column formula

=if(Query1[age]<=12,"Below 12",if(Query1[age]<=19,"12-19",if(Query1[age]<=29,"20-29",if(Query1[age]<=49,"30-49","Above 50"))))

Hope this helps.

[edhans]

@Anonymous - You cannot use a column like that in a measure. Wrap all of those with MAX(). That will convert the column to a scalar value.

So IF(MAX(Query1[Age]) > 3, etc.

If you want to create a calculed column that concatenates those fields use this:

Column3 = Table[Column1] & " " & Table[Column2]

Replace "Table" with your table name.

If the data is in Power Query I highly advise you do the concatenation there. You can just select Column1 and Column2, and click either on Add Column or Transform ribbons, and select merge, and use a space as the delimiter.

The difference is Add Column will create a new column and leave the old two in place. Transform will add the new column and remove the other two.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns
Creating a Dynamic Date Table in Power Query

 

[Anonymous]

I try using MAX() but I get this error instead.I am not sure what is wrong,

I couldn't edit the power query for some reasons

[v-shex-msft]

Hi @Anonymous,

According to the error message, it looks like your are try to use math operators to compare text and numeric value which power bi not supported. I'd like to suggest you change the source field data type or try to add data convert step into the expressions.

I modify the Ashish_Mathur 's formula to add the value convert step, you can try to use the following calculated column expressions if it meets your requirement.

Category =
VAR age =
    VALUE ( Query1[age] )
RETURN
    IF (
        age <= 12,
        "Below 12",
        IF (
            age <= 19,
            "12-19",
            IF ( age <= 29, "20-29", IF ( age <= 49, "30-49", "Above 50" ) )
        )
    )

Regards,

Xiaoxin Sheng

[edhans]

---

@Anonymous wrote:

I try using MAX() but I get this error instead.I am not sure what is wrong,

I couldn't edit the power query for some reasons

---

I think your [Age] column is formatted as text. Can you confirm it is numerical? You can set the data type properly in Power Query. It is the tiny icon in the upper left of each column.

And what do you mean @Anonymous "couldn't edit the power query for some reason?" What happens when you click on the TRANSFORM DATA button in the home ribbon of the desktop app?

[amitchandak]

@Anonymous ,Can you share a sample pbix after removing sensitive data.

[amitchandak]

@Anonymous , Hope you are creating a new column and have age column

Try like

Switch( True(),
[age] < 12, "Below 12",
[age]<=19 , "Between 12 to 19 ",
[age]<=29 , "Between 19 to 29 ",
[age]<=49 , "Between 29 to 49 ",
"Above 49"
)

If age is the measure then you need to do bucketing. Please refer to my video on segmentation for that https://www.youtube.com/watch?v=CuczXPj0N-k

[Anonymous]

@amitchandak

I have the age column in my dataset already.

I tried using the switch statement

but I still cannot use the age column in my statement

in the mean time, I will watch your video

Thank you!