Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sort by Columns

Hello guys,     I created a calculated column which helped me to categorize the tenure of my employees. The results are the following   0-30 days  31-60 days  61-90 days 91-180 days 181- 365...
Need Help
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Anonymous 

    Try changing them to this.

     

    Tenure =
VAR _Days =
    TODAY () - Agents[Production Date].[Date]
RETURN
    SWITCH (
        TRUE (),
        _Days <= 30, "0-30 Days",
        _Days <= 60, "31-60 Days",
        _Days <= 90, "61-90 Days",
        _Days <= 180, "91-180 Days",
        _Days <= 365, "181-365 Days",
        "+365 Days"
    )

    Tenure ID =
VAR _Days =
    TODAY () - Agents[Production Date].[Date]
RETURN
    SWITCH (
        TRUE (),
        _Days <= 30, 1,
        _Days <= 60, 2,
        _Days <= 90, 3,
        _Days <= 180, 4,
        _Days <= 365, 5,
        6
    )
jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Hello Anonymous 

When you created your [Tenure ID] column you say you used the [Tenure Category] (your first calculated column) yes?

Can you share the two DAX formulas you used to make the columns?

 

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    I created a sample table and made my category and two sort columns.  On the first sort column I used the category, on the second I replicated the category logic to get the sort.

    Because my first sort field looks at the category field I cannot sort the category field using it.  I am basically telling it to sort category by category.

    If I sort category by the second sort, the one that does not look at the category field but instead uses the same logic as the category field to determine the sort value it works fine.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Tenure = IF(
           TODAY() - Agents[Production Date].[Date] <= 30, "0-30 Days", 
             IF(AND(TODAY() - Agents[Production Date].[Date] > 30,TODAY() - Agents[Production Date].[Date] <= 60), "31-60 Days", 
               IF(AND(TODAY() - Agents[Production Date].[Date] > 60, TODAY() - Agents[Production Date].[Date] <= 90), "61-90 Days", 
                 IF(AND(TODAY() - Agents[Production Date].[Date] >90, TODAY() - Agents[Production Date].[Date] <= 180), "91-180 Days", 
                IF(AND(TODAY() - Agents[Production Date].[Date] >180, TODAY() - Agents[Production Date].[Date] <= 365), "181-365 Days", "+365 Days")))))


Tenure ID = SWITCH(Agents[Tenure],
                    "0-30 Days", 1,
                        "31-60 Days", 2,
                          "61-90 Days",3,
                            "91-180 Days", 4,
                                "181-365 Days", 5,
                                    6)
    • jdbuchanan71's avatar
      jdbuchanan71
      Super User
      6 years ago

      Anonymous 

      Try changing them to this.

       

      Tenure =
VAR _Days =
    TODAY () - Agents[Production Date].[Date]
RETURN
    SWITCH (
        TRUE (),
        _Days <= 30, "0-30 Days",
        _Days <= 60, "31-60 Days",
        _Days <= 90, "61-90 Days",
        _Days <= 180, "91-180 Days",
        _Days <= 365, "181-365 Days",
        "+365 Days"
    )

      Tenure ID =
VAR _Days =
    TODAY () - Agents[Production Date].[Date]
RETURN
    SWITCH (
        TRUE (),
        _Days <= 30, 1,
        _Days <= 60, 2,
        _Days <= 90, 3,
        _Days <= 180, 4,
        _Days <= 365, 5,
        6
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        jdbuchanan71  Thanks so much for that, and also to remind me the use of variables to simplify the code haha