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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Lily_Lai346
New Member

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,

 

Lily_Lai346_0-1701357369143.png

 

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

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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],
    ", "
)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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],
    ", "
)

Thanks so much Amit, it works!

 

Cheers,

Lily

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.