Forum Discussion

MaleneL's avatar
MaleneL
Resolver I
2 years ago
Solved

finding customer with two different status

Hoping for help  I have at table where at customer the same month can have two status' and i want two make at dax finding the customer wich have or also have a Leavers status:   Anyone that ...
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    You need to create a DAX measure or calculated column that will count the number of distinct statuses for each customer in a given month. If the count is greater than 1 and one of the statuses is 'leavers', then you can mark that customer as "Also Leavers".

     

    Also Leavers Column =
VAR CurrentCustomer = [Customer ID]
VAR CurrentMonth = MONTH([date])
VAR CurrentYear = YEAR([date])
VAR LeaversCount =
CALCULATE(
COUNTROWS(
FILTER(
'TableName',
'TableName'[Customer ID] = CurrentCustomer &&
MONTH('TableName'[date]) = CurrentMonth &&
YEAR('TableName'[date]) = CurrentYear &&
'TableName'[status] = "leavers"
)
)
)
VAR TotalStatus =
CALCULATE(
COUNTROWS(
FILTER(
'TableName',
'TableName'[Customer ID] = CurrentCustomer &&
MONTH('TableName'[date]) = CurrentMonth &&
YEAR('TableName'[date]) = CurrentYear
)
)
)
RETURN
IF(LeaversCount > 0 && TotalStatus > 1, "Also Leavers", BLANK())
AmiraBedh's avatar
AmiraBedh
Super User
2 years ago

You need to create a DAX measure or calculated column that will count the number of distinct statuses for each customer in a given month. If the count is greater than 1 and one of the statuses is 'leavers', then you can mark that customer as "Also Leavers".

 

Also Leavers Column =
VAR CurrentCustomer = [Customer ID]
VAR CurrentMonth = MONTH([date])
VAR CurrentYear = YEAR([date])
VAR LeaversCount =
CALCULATE(
COUNTROWS(
FILTER(
'TableName',
'TableName'[Customer ID] = CurrentCustomer &&
MONTH('TableName'[date]) = CurrentMonth &&
YEAR('TableName'[date]) = CurrentYear &&
'TableName'[status] = "leavers"
)
)
)
VAR TotalStatus =
CALCULATE(
COUNTROWS(
FILTER(
'TableName',
'TableName'[Customer ID] = CurrentCustomer &&
MONTH('TableName'[date]) = CurrentMonth &&
YEAR('TableName'[date]) = CurrentYear
)
)
)
RETURN
IF(LeaversCount > 0 && TotalStatus > 1, "Also Leavers", BLANK())
  • MaleneL's avatar
    MaleneL
    Resolver I
    2 years ago

    Hi Amira
    Thanks for your reply. Unfortunately I cannot get it to work.

    The LeaversCount work

    And

    The TotalStatus work

    But if I have a row where a customer have two in TotalStatus and 1 in LeaversCount it doesn’t say Also Leavers as it should…

    Any idea why

    Also Leavers =
    VAR Kunde = 'Fact_DLR_Omlægninger'[KundenrKey]
    VAR _CurrentMonth = MONTH('Fact_DLR_Omlægninger'[Slutdato])
    VAR _CurrentYear = YEAR('Fact_DLR_Omlægninger'[Slutdato])
    VAR LeaversCount =
            CALCULATE(
                COUNTROWS(
                    FILTER('Fact_DLR_Omlægninger', 'Fact_DLR_Omlægninger'[KundenrKey] = Kunde &&
                    MONTH('Fact_DLR_Omlægninger'[Slutdato]) = _CurrentMonth &&
                    YEAR('Fact_DLR_Omlægninger'[Slutdato]) = _CurrentYear &&
                    'Fact_DLR_Omlægninger'[Status] = "AFGANG")
    ))

    VAR TotalStatus =
            CALCULATE(
                COUNTROWS(
                    FILTER('Fact_DLR_Omlægninger', 'Fact_DLR_Omlægninger'[KundenrKey] = Kunde &&
                    MONTH('Fact_DLR_Omlægninger'[Slutdato]) = _CurrentMonth &&
                    YEAR('Fact_DLR_Omlægninger'[Slutdato]) = _CurrentYear)
    ))
    RETURN
        IF(LeaversCount > 0 && TotalStatus > 1, "Also Leavers", BLANK())


    • AmiraBedh's avatar
      AmiraBedh
      Super User
      2 years ago

      Can you please share your pbix ?

      • MaleneL's avatar
        MaleneL
        Resolver I
        2 years ago

        I have to built it it is confidential data. I will try to do it tomorrow