Forum Discussion

bapt69's avatar
bapt69
Icon for Helper I rankHelper I
3 years ago
Solved

Related with sorted agregated table

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

Need Help
Show and Tell
  • bapt69's avatar
    bapt69
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      bapt69
      Icon for Helper I rankHelper I
      3 years ago

      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