Forum Discussion

MaleneL's avatar
MaleneL
Icon for Resolver I rankResolver I
3 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
    3 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
Icon for Super User rankSuper User
3 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
Icon for Resolver I rankResolver I
3 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
    Icon for Super User rankSuper User
    3 years ago

    Can you please share your pbix ?

    • MaleneL's avatar
      MaleneL
      Icon for Resolver I rankResolver I
      3 years ago

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