Update weekday title dynamically

[bergen288]

My 'calendar' is dimentional table with [Week Day] (from Sunday to Saturday) and [Weeknum] (from 1 to 7) columns.  It has 1:* relationship with my fact table and [Week Day] is used as slicer in the chart.    Below is my measure for chart title and chart screentshot.  I would like to replace "Saturday, Sunday" with "Weekends" if Saturday and Sunday are selected.  Similarly, replace "Monday...Friday" to "Weekdays" if 5 weekdays are selected.  All others are good.  How to address it? 

Weekday Hourly Profile Title =

    VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('calendar'[Week Day])

    VAR __CONDITION = ISFILTERED('calendar'[Week Day])

    VAR __PREFIX ="Payment Hourly Profile"

    VAR __LIST =

        __PREFIX & " on " &

        CONCATENATEX(

            VALUES('calendar'[Week Day]),

            'calendar'[Week Day],

            ", ",

            SELECTEDVALUE('calendar'[Weeknum]),

            ASC

        )

    RETURN

    IF(

        __CONDITION,

        __LIST,

        __PREFIX & " for Whole Week"

    )

[johnt75]

try 

Weekday Hourly Profile Title =
VAR __DISTINCT_VALUES_COUNT =
    DISTINCTCOUNT ( 'calendar'[Week Day] )
VAR __CONDITION =
    ISFILTERED ( 'calendar'[Week Day] )
VAR __PREFIX = "Payment Hourly Profile"
VAR __LIST =
    __PREFIX & " on "
        & SWITCH (
            TRUE (),
            COUNTROWS ( INTERSECT ( VALUES ( 'Calendar'[Weeknum] ), { 1, 7 } ) ) = 2, "weekends",
            COUNTROWS ( INTERSECT ( VALUES ( 'Calendar'[Weeknum] ), { 2, 3, 4, 5, 6 } ) ) = 5, "weekdays",
            CONCATENATEX (
                VALUES ( 'calendar'[Week Day] ),
                'calendar'[Week Day],
                ", ",
                SELECTEDVALUE ( 'calendar'[Weeknum] ), ASC
            )
        )
RETURN
    IF ( __CONDITION, __LIST, __PREFIX & " for Whole Week" )

[bergen288]

Well, it sort of works, but not perfectly.  For example, if Sunday, Monday, and Saturday are selected, the title will show Weekends.  The same is true for "Weekdays":  if from Monday to Saturday are selected, it will show "Weekdays".

[johnt75 · Accepted solution]

This should work I think

Weekday Hourly Profile Title =
VAR __DISTINCT_VALUES_COUNT =
    DISTINCTCOUNT ( 'calendar'[Week Day] )
VAR __CONDITION =
    ISFILTERED ( 'calendar'[Week Day] )
VAR __PREFIX = "Payment Hourly Profile"
VAR __LIST =
    __PREFIX & " on "
        & SWITCH (
            TRUE (),
            COUNTROWS ( INTERSECT ( VALUES ( 'Calendar'[Weeknum] ), { 1, 7 } ) ) = 2
                && COUNTROWS ( VALUES ( 'Calendar'[Weeknum] ) ) = 2, "weekends",
            COUNTROWS ( INTERSECT ( VALUES ( 'Calendar'[Weeknum] ), { 2, 3, 4, 5, 6 } ) ) = 5
                && COUNTROWS ( VALUES ( 'Calendar'[Weeknum] ) ) = 5, "weekdays",
            CONCATENATEX (
                VALUES ( 'calendar'[Week Day] ),
                'calendar'[Week Day],
                ", ",
                SELECTEDVALUE ( 'calendar'[Weeknum] ), ASC
            )
        )
RETURN
    IF ( __CONDITION, __LIST, __PREFIX & " for Whole Week" )

[bergen288]

great, it works perfectly this time. 

Thanks a lot.

[FreemanZ]

hi @bergen288 

what do you expect to show if both Sunday and Monday are selected?

[bergen288]

It's not me.  I have to prevent such kind of issue for end users.