Related with sorted agregated table

[bapt69]

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

[v-xinruzhu-msft]

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.}

[bapt69 · Accepted 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