Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Creating category column for customer

Hi all,   Due to my beginner knowledge of DAX,  I would like to ask for help with creating Category collumn for customers in PBI table view. I have tried AI, but after hours of trying, ending up st...
  • techies's avatar
    techies
    1 year ago

    Hi Anonymous please try this calculated column

     

    Customer Category =
    VAR Has_Oct_Dec =
        CALCULATE(
            SUM(Sheet6[Volume]),
            ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code]),
            Sheet6[year month] IN {"2024-10", "2024-11", "2024-12"}
        ) > 0

    VAR Has_Only_Zero_Jan_Feb =
        CALCULATE(
            SUM(Sheet6[Volume]),
            ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code]),
            Sheet6[year month] IN {"2025-01", "2025-02"}
        ) = 0

    VAR Has_Mar =
        CALCULATE(
            SUM(Sheet6[Volume]),
            ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code]),
            Sheet6[year month] = "2025-03"
        ) > 0

    VAR Has_All_Months =
       
        CALCULATE(
            COUNTROWS(FILTER(Sheet6, Sheet6[Volume] > 0)),
            ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code])
        ) = DISTINCTCOUNT(Sheet6[year month])

    RETURN
    SWITCH(
        TRUE(),
        Has_All_Months, "Constant",
        Has_Mar && NOT Has_Oct_Dec && Has_Only_Zero_Jan_Feb, "New",
        Has_Oct_Dec && Has_Only_Zero_Jan_Feb && Has_Mar, "Renewed",
        "Other"
    )
techies's avatar
techies
Icon for Super User rankSuper User
1 year ago

Hi Anonymous please try this calculated column

 

Customer Category =
VAR Has_Oct_Dec =
    CALCULATE(
        SUM(Sheet6[Volume]),
        ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code]),
        Sheet6[year month] IN {"2024-10", "2024-11", "2024-12"}
    ) > 0

VAR Has_Only_Zero_Jan_Feb =
    CALCULATE(
        SUM(Sheet6[Volume]),
        ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code]),
        Sheet6[year month] IN {"2025-01", "2025-02"}
    ) = 0

VAR Has_Mar =
    CALCULATE(
        SUM(Sheet6[Volume]),
        ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code]),
        Sheet6[year month] = "2025-03"
    ) > 0

VAR Has_All_Months =
   
    CALCULATE(
        COUNTROWS(FILTER(Sheet6, Sheet6[Volume] > 0)),
        ALLEXCEPT(Sheet6, Sheet6[Customer ID], Sheet6[Location Code])
    ) = DISTINCTCOUNT(Sheet6[year month])

RETURN
SWITCH(
    TRUE(),
    Has_All_Months, "Constant",
    Has_Mar && NOT Has_Oct_Dec && Has_Only_Zero_Jan_Feb, "New",
    Has_Oct_Dec && Has_Only_Zero_Jan_Feb && Has_Mar, "Renewed",
    "Other"
)
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hello Techies, 

Thank you very much, this one is working:)

BR,

Dominik