Hello everyone,
I don't know which subject I have to write...
I got a table with my customers (with client_id) :
and a table with the historic (with client_id -> active relationship with customers.client_id) :
And obviously a Date Table (with date_id -> inactive relationships with date_debut and date_fin)
I would like filled "Type_statut" if the customer has or not one or an other status during a period.
If the customer had one or more rows with CLUB an only CLUB with date_debut>date and date_fin<date -> type statut = CLUB
If the customer had one or more rows with PLAT1 or EMER with date_debut>date and date_fin<date -> type statut = VIP
If the customer had only STD or PRSP -> Type_statut = ANONYME
I don't know if it's clear enough...
Tell me
Thank you in advance,
Baptiste
Solved! Go to Solution.
Sorry, I fixed it...
I got a table with my status and a level for each one.
So, I create a measure that get the max level status :
Max_statut = CALCULATE(
MAX(d_client_histo[Niveau_statut]),
FILTER(d_client_histo,
d_client[client_id]=d_client_histo[client_id]
&&d_client_histo[date_fin]>=(TODAY()-365)))
Then, I created a measure that check this max to fill the good type_status :
Type_statut = IF(
d_client[Max_statut]=100,"CLUB",IF(d_client[Max_statut]>100,"VIP",IF(d_client[Max_statut]<100,"Anonyme","")))
Thank you for your answer and your help!!
Baptiste
Hi @bapt69 ,
Perhaps you can refer to the following code, but the following code may not fully achieve the effect you want, I have a few questions can provide a specific reference:
1.Is your date table used for slicer filtering?
2.The conditions you provide are date_debut> date_fin, but the date_fin in the picture are greater than date_debut, whether it is only partially displayed
3.If date is used as a filter, it is recommended here that measures should be used instead of calculated columns
Code
Type_status =
var a=COUNTAX(RELATEDTABLE('Table (2)'),[staus_club])
return SWITCH(TRUE(),[statut_club]="CLUB"&&a>0,"CLUB",[statut_club]="PLAT1"&&a>0,"VIP",[statut_club]="PRSP","ANONYME",BLANK())
Output:
Best Regards,
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry, I fixed it...
I got a table with my status and a level for each one.
So, I create a measure that get the max level status :
Max_statut = CALCULATE(
MAX(d_client_histo[Niveau_statut]),
FILTER(d_client_histo,
d_client[client_id]=d_client_histo[client_id]
&&d_client_histo[date_fin]>=(TODAY()-365)))
Then, I created a measure that check this max to fill the good type_status :
Type_statut = IF(
d_client[Max_statut]=100,"CLUB",IF(d_client[Max_statut]>100,"VIP",IF(d_client[Max_statut]<100,"Anonyme","")))
Thank you for your answer and your help!!
Baptiste
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!