Forum Discussion

bergen288's avatar
bergen288
Helper IV
3 years ago
Solved

Update weekday title dynamically

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

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

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

6 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      bergen288
      Helper IV
      3 years ago

      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's avatar
        johnt75
        Super User
        3 years ago

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