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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
mmp
Helper I
Helper I

Filtering measure based on another measure

Hello,

 

I'm trying to get a list of client that has not been in the store for the previous month (based on date selection) and has bought at least during the period day-40 and day-59. Here is the script:

 

Max Achat Date = 
CALCULATE(
    MAX(BITKT[TKT_DATACHAT]),
    ALLEXCEPT(BITKT,BITKT[TKT_NUMCARTE])
)



Duration = 
VAR tday = IF(HASONEVALUE(BIDAT[DAT_date]),FIRSTDATE(BIDAT[DAT_date]))
RETURN  
IF (
    [Max Achat Date] > tday,
    0,
    DATEDIFF ( [Max Achat Date], tday, DAY )
)


Win Back 1 = VAR day_40 = DATEADD(BIDAT[DAT_date],-40,DAY)
VAR day_59 = DATEADD(BIDAT[DAT_date],-59,DAY) 
VAR quantite = CALCULATE(SUM(BITKT[TKT_QTE]),DATESBETWEEN(BIDAT[DAT_date],day_59,day_40))
RETURN

IF([Duration]>30 && quantite>1, 1,0)

 

What I'm trying to do now is to distinct count the number of people that are concerned by the Win Back = 1.

 

Capture d’écran 2020-10-16 à 10.13.08.png

 

I tried with SUMX, COUNTAX but no results.

 

Could you please help me on that ?

 

Kind regards,

 

Mohammad PATEL

 

2 REPLIES 2
amitchandak
Super User
Super User

@mmp , Try measure  like

sumx(values(Table[N Cate]),[Win Back 1])

or

sumx(Summarize(Table,Table[N Cate], "_1",[Win Back 1]),[_1])

Hi @amitchandak

 

I tried your solutions but no results, it returns 0.

 

Mohammad PATEL

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.