Forum Discussion

Lily_Lai346's avatar
Lily_Lai346
New Member
2 years ago
Solved

Dax Formula Help needed, Thanks!

Hi all,

I have a data set that shows multiple forms downloaded by prospects, the fields used were created date/time, contact id and program names.  The data can show multiple lines of same contact id with different date/time and each line shows different program names. 

The desired result is to show something like this:

 

contact id     Program of Interest 

xxxxx             WLP, AMLP, SDM, LCT ( program name to show based on earliest created date/time )

I will also like to split the Program name afterwards in different columns like below,

 

 

I am not sure if below formula is the best way to achieve the above result but I think it didnt take into consideration the contact id which I am stuck, hence any advice will be greatly appreciated! 

Programme of Interest by Creation Date = CONCATENATEX(FILTER(SUMMARIZE(MFSA,MFSA[Created Date/Time (FRA)],
MFSA[Program Name]),MFSA[Created Date/Time (FRA)]=EARLIER(MFSA[Created Date/Time (FRA)])),
MFSA[Program Name],",")
 
Thanks,
Lily

 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    Lily_Lai346 , Based on what I got, check if this can help 

    Programme of Interest by Creation Date = 
CONCATENATEX(
    FILTER(
        SUMMARIZE(
            MFSA, 
            MFSA[Contact ID], 
            "EarliestDate", MIN(MFSA[Created Date/Time (FRA)]), 
            MFSA[Program Name]
        ),
        [EarliestDate] = CALCULATE(MIN(MFSA[Created Date/Time (FRA)]), ALLEXCEPT(MFSA, MFSA[Contact ID]))
    ),
    MFSA[Program Name],
    ", "
)

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Lily_Lai346 , Based on what I got, check if this can help 

    Programme of Interest by Creation Date = 
CONCATENATEX(
    FILTER(
        SUMMARIZE(
            MFSA, 
            MFSA[Contact ID], 
            "EarliestDate", MIN(MFSA[Created Date/Time (FRA)]), 
            MFSA[Program Name]
        ),
        [EarliestDate] = CALCULATE(MIN(MFSA[Created Date/Time (FRA)]), ALLEXCEPT(MFSA, MFSA[Contact ID]))
    ),
    MFSA[Program Name],
    ", "
)