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 can help?

Malene

 

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())

4 Replies

  • 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())