Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
3 years ago
Solved

Date Bandings require sorting in visual

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
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    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

     

12 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You could add a column like

    Group sort column =
SWITCH (
    'Table'[Age Profile Creation Months],
    "0-3 months", 1,
    "3-6 months", 2
)
    • ArchStanton's avatar
      ArchStanton
      Icon for Power Participant rankPower Participant
      3 years ago

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

       

       

  • ArchStanton's avatar
    ArchStanton
    Icon for Power Participant rankPower Participant
    3 years ago

    This is my other attempt based directly on what you suggested

     

     

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

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

      • ArchStanton's avatar
        ArchStanton
        Icon for Power Participant rankPower Participant
        3 years ago

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

         

         

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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