CONCATENATEX with Only Relevant Values

[db_programmer]

I have a SUMMARIZECOLUMNS query that produces this table:

Block ID	Year Month	Day of Week - Abbreviation	Week Number	Total Used	Total Unused	Total Available	Early Time	Late Time
72	202301	Mon	2	1557	-327	1230	730	1500
72	202301	Wed	2	1 770	-420	1350	730	1500
72	202301	Wed	1	1286	64	1350	730	1500
72	202301	Mon	5	1488	-138	1350	730	1500
72	202301	Mon	4	2044	-694	1350	730	1500
72	202301	Wed	4	980	370	1350	730	1500

I would like to summarize this further so that it is only two rows, one for each of the two days of the week in the dataset, e.g., Mon and Wed.  The Weeks column should be a concatenated list of the weeks, e.g., Mon would be  "2,4,5" and Wed would be "1,2,4":

Block ID	Year Month	Day of Week - Abbreviation	Weeks	Total Used	Total Unused	Total Available	Early Time	Late Time
72	202301	Mon	2,4,5	1276	74	1350	730	1500
72	202301	Wed	1,2,4	1033	317	1350	730	1500

I've tried replacing the [Week Number] column with a new column [Weeks] using the CONCATENATEX function but it won't even run:

 EVALUATE
VAR PLACEMAT_PRELIM =
    SUMMARIZECOLUMNS (
        'Block'[Block ID],
        'Calendar'[Year Month],
        'Calendar'[Day of Week - Abbreviation],
       -- 'Calendar'[Week Number],
        "Weeks",CONCATENATEX(FILTER(Calendar,[Day of Week - Abbreviation]=EARLIER([Day of Week - Abbreviation)),Calendar[Week Number],", " ),
       FILTER (
            'Hourly Block Utilization Util Group Scenario',
            'Hourly Block Utilization Util Group Scenario'[UtilGroupScenario] = "Performed"
        ),
        FILTER ( 'Block', 'Block'[Block ID] = 72 ),
        FILTER ( 'Calendar', 'Calendar'[Year Month] = "202301" ),
        "Week",CONCATENATEX(
        "Total Used", [NetBlockUtilUsedTime],
        "Total Unused", [netblockutilunusedtime],
        "Total Available", [NetBlockUtilAvailableTime],
        "Early Time",
            IGNORE (
                MIN ( 'Hourly Block Utilization Denominator'[OverallSlotStartTimeKey] )
            ),
        "Late Time", IGNORE ( MAX ( 'Hourly Block Utilization Denominator'[OverallSlotEndTimeKey] ) )
    )
    )
   
RETURN PLACEMAT_PRELIM

If I write the [Weeks] column like below, it returns ALL the weeks in the month for the given day of the week, not just the ones that are in the intilial SUMARIZECOLUMNS table:

  "Weeks",
            IGNORE (
                CONCATENATEX (
                    FILTER (
                        VALUES ( 'Calendar'[Week Number] ),
                        NOT ISBLANK ( MAX ( 'Calendar'[Day of Week - Abbreviation] ) )
                    ),
                    'Calendar'[Week Number],
                    ","
                )
            ),

What's a better way to approach this?

[Dangar332]

hi, @db_programmer 

try below cofe for new table

just adjust your column and table name

SUMMARIZE(
	'Table',
	'Table'[Block ID],
	'Table'[Year Month],
	'Table'[Day of Week - Abbreviation],
	'Table'[Early Time],
	'Table'[Late Time],
	"Total used",sum('Table'[Total Used]),
	"total unused",SUM('Table'[Total Unused]),
	"total avilable",SUM('Table'[Total Available]),
	"Weeks",
	  var a= VALUES('Table'[Week Number])
	  RETURN
	   CONCATENATEX(a,'Table'[Week Number],", "))
	

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[db_programmer · Accepted solution]

Thank you.  I couldn't get yours to work completely - it was still showing all the weeks of the month relevant to that calendar date, but not the weeks relevant to the data in each row.  I figured out another way, inspired by your idea of recalculating the totals, using the GROUPBY table function and then finally the ADDCOLUMNS table function.  I think the key piece is the CONCATENATEX function is not referencing the genric Calendar table but the specific table variable created in step 1.  Thank you for your suggestion.

EVALUATE
VAR PLACEMAT_1 =
    SUMMARIZECOLUMNS (
        'Block'[Block ID],
        'Calendar'[Year Month],
        'Calendar'[Day of Week - Abbreviation],
        'Calendar'[Week Number],
        FILTER ( 'Block', 'Block'[Block ID] = 72 ),
        FILTER ( 'Calendar', 'Calendar'[Year Month] = "202301" ),
        "Total Used", [NetBlockUtilUsedTime],
        "Total Unused", [netblockutilunusedtime],
        "Total Available", [NetBlockUtilAvailableTime],
        "Early Time",
            IGNORE (
                MIN ( 'Hourly Block Utilization Denominator'[OverallSlotStartTimeKey] )
            ),
        "Late Time", IGNORE ( MAX ( 'Hourly Block Utilization Denominator'[OverallSlotEndTimeKey] ) )
    )
VAR PLACEMAT_2 =
    GROUPBY (
        PLACEMAT_1,
        [Block ID],
        [Year Month],
        [Day of Week - Abbreviation],
        "Total Used", SUMX ( CURRENTGROUP (), [Total Used] ),
        "Total Unused", SUMX ( CURRENTGROUP (), [Total Unused] ),
        "Total Available", SUMX ( CURRENTGROUP (), [Total Available] ),
        "Early Time", MINX ( CURRENTGROUP (), [Early Time] ),
        "Late Time", MAXX ( CURRENTGROUP (), [Late Time] )
    )
RETURN
    ADDCOLUMNS (
        PLACEMAT_2,
        "Weeks",
            CONCATENATEX (
                FILTER (
                    PLACEMAT_1,
                    [Day of Week - Abbreviation] = EARLIER ( [Day of Week - Abbreviation] )
                ),
                [Week Number],
                ",",
                [Week Number]
            )
    )