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?

 

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