Date Bandings require sorting in visual

[ArchStanton]

Hi,

I have various date bands ranging from 0-3mths, 3-6 and so on all the way up to 12 mths +.

These are not sorted when I add them to the Y axis in my visual so I was thinking of ranking them 1 to 5 and then sorting them that way. Is this the best method to do thi?

I thought I could write a NESTEDIF Statement but it isn't working:

RANK = IF([Age Profile Creation Months] = "0-3 Months",1, IF([Age Profile Creation Months] = "3-6 Months",2, IF([Age Profile Creation Months] = "6-9 Months",3,IF([Age Profile Creation Months] = "9-12 Months",4, IF([Age Profile Creation Months] = "12+ Months",5, "")))))

Any ideas anyone?

Thanks

[v-luwang-msft · Accepted solution]

Hi @ArchStanton ，

Pls test the below:

RANK = 
IF (
    [Age Profile Creation Months] = "0-3 Months",
    1,
    IF (
        [Age Profile Creation Months] = "3-6 Months",
        2,
        IF (
            [Age Profile Creation Months] = "6-9 Months",
            3,
            IF (
                [Age Profile Creation Months] = "9-12 Months",
                4,
                IF ( [Age Profile Creation Months] = "12+ Months", 5,BLANK() )
            )
        )
    )
)

Return:

Best Regards

Lucien

[ArchStanton]

This is my other attempt based directly on what you suggested

[johnt75]

replace the "N/A" with 6. You can't return an integer and a string into the same column.

[ArchStanton]

It still has the same error when N/a is replace with 6

[johnt75]

that's still a string, you need to return the number 6 without the quotes

[ArchStanton]

I've tried lots of variations and the error just keeps appearing:

[johnt75]

the switch statement should not have any = signs in it. the first argument should be the column name, then a list of pairs of values separated by commas, see my original post for the template.

[ArchStanton]

Thanks, I managed to get it to work but its only given my 5's and 6's for some reason?

In the meantime I've copied the original Date Banding Column - modified it by substituting Date Bands for No's 1-5 and then applied a Sort on the column and this seems to have done the trick. I'd still like to know why the SWITCH code is ignoring 1,2,3,4 though?

[johnt75]

the strings would need to match exactly - same capitalization of letters, same number of spaces etc.

[ArchStanton]

[johnt75]

You could add a column like

Group sort column =
SWITCH (
    'Table'[Age Profile Creation Months],
    "0-3 months", 1,
    "3-6 months", 2
)

[ArchStanton]

Thanks but something isn't working right with my code: