Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bapt69
Helper I
Helper I

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) :

CLient_id.jpg

 

and a table with the historic (with client_id -> active relationship with customers.client_id) :

Client_histo.jpg

 

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

1 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

View solution in original post

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

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:

vxinruzhumsft_0-1670395577533.png

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors