Forum Discussion
MaleneL
Resolver I
Resolver Ifinding 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 ...
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())
MaleneLResolver I
Resolver IHi 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())
