Forum Discussion

bergen288's avatar
bergen288
Icon for Helper IV rankHelper 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
Icon for Super User rankSuper 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
Icon for Helper IV rankHelper 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
    Icon for Super User rankSuper 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
      Icon for Helper IV rankHelper IV
      3 years ago

      great, it works perfectly this time. 

       

      Thanks a lot.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi bergen288 

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

    • bergen288's avatar
      bergen288
      Icon for Helper IV rankHelper IV
      3 years ago

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