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 c...
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" )
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" )
      • bergen288's avatar
        bergen288
        Helper IV
        3 years ago

        great, it works perfectly this time. 

         

        Thanks a lot.

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      hi bergen288 

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

      • bergen288's avatar
        bergen288
        Helper IV
        3 years ago

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