Forum Discussion

db_programmer's avatar
db_programmer
Frequent Visitor
2 years ago
Solved

CONCATENATEX with Only Relevant Values

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 ...
  • db_programmer's avatar
    db_programmer
    2 years ago

    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]
            )
    )

     

Dangar332's avatar
Dangar332
Icon for Resident Rockstar rankResident Rockstar
2 years ago

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's avatar
    db_programmer
    Frequent Visitor
    2 years ago

    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]
            )
    )