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

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

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],
    ", "
)
Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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],
    ", "
)
Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks so much Amit, it works!

 

Cheers,

Lily

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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