Search code examples
powerbidaxssas-tabulardaxstudio

Group Items in Comma Separated values in Dax


I have following DAX code. I need comma separated holiday list based on Week No . I tired with CONCATENATEX function and it combine all comma separated holiday list in each row instead of week wise. Any one have any idea how we can achieve below expected using DAX.

Dax Code:

DEFINE
VAR A = UNION(   ROW ("WeekNo",  1, "USAHoliday", "New Year", "CANHoliday","New Year")
                ,ROW ("WeekNo",  16, "USAHoliday", "Easter Sunday", "CANHoliday","Easter Sunday")
                ,ROW ("WeekNo",  16, "USAHoliday", "", "CANHoliday","Easter Monday")
                ,ROW ("WeekNo",  27,  "USAHoliday", "Independence Day", "CANHoliday","")
                ,ROW ("WeekNo",  28,  "USAHoliday", "", "CANHoliday","Independence Day")
                ,ROW ("WeekNo",  22,  "USAHoliday", "Memorial Day", "CANHoliday","")
            )

VAR AHoliday = DISTINCT(FILTER( UNION(SELECTCOLUMNS(A,
                        "WeekNo",[WeekNo]
                        ,"Holiday",[USAHoliday]
                        ),
                SELECTCOLUMNS(A,
                        "WeekNo",[WeekNo]
                        ,"Holiday",[CANHoliday]
                        )
                ),[Holiday]<>""))

VAR HolidayList=SUMMARIZE(AHoliday
            ,[WeekNo]
            ,"HolidayList.", CONCATENATEX(AHoliday,[Holiday],",", [Holiday],ASC)
            )
            
EVALUATE HolidayList

Expected Result

enter image description here


Solution

  • HolidayList should be defined as, for example:

    SUMMARIZE(
        AHoliday,
        [WeekNo],
        "HolidayList",
            CONCATENATEX(
                FILTER( AHoliday, [WeekNo] = EARLIER( [WeekNo] ) ),
                [Holiday],
                ", "
            )
    )