Forum Discussion
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...
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" )
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".
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" )
great, it works perfectly this time.
Thanks a lot.