Power BI Dax Multiple IF AND Statements

Question

I have a DAX query in Power BI - see below. I have created a new column in the data and I want to Group AgeWhenFirstSold(Mo) into Age buckets.

The result I get from this query is 0-5 Months but the 6-11 Months doesn't show in the column

Group AgeWhenFirstSold(Mo)- 6 Months =
IF (
    AND ( Sheet1[AgeWhenFirstSold(Mo)] >= "0", Sheet1[AgeWhenFirstSold(Mo)] <= "5" ),
    "0 - 5 Months",
    IF (
        AND ( Sheet1[AgeWhenFirstSold(Mo)] > "5", Sheet1[AgeWhenFirstSold(Mo)] <= "11" ),
        "6 - 11 Months"
    )
)

TDP TDP · Accepted Answer · 2018-05-09T10:21:59

I suspect your issue is that alphabetically "11" <= "5"

Try using VALUE(<text>) "Converts a text string that represents a number to a number."

e.g. VALUE( Sheet1[AgeWhenFirstSold(Mo)] ) >= 0

However you can also configure bins and groups to avoid having to extract them with DAX:

Right-click AgeWhenFirstSold(Mo) in the FIELDS section of Power BI Desktop and choose New Group:

A dialog then allows you to configure your new group.

An example:

Power BI Dax Multiple IF AND Statements

1 Answers