topic Re: Update weekday title dynamically in DAX Commands and Tips

Update weekday title dynamically

bergen288 — Thu, 05 Jan 2023 16:20:22 GMT

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"

)

Re: Update weekday title dynamically

johnt75 — Thu, 05 Jan 2023 17:21:30 GMT

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

Re: Update weekday title dynamically

bergen288 — Thu, 05 Jan 2023 19:47:57 GMT

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

Re: Update weekday title dynamically

FreemanZ — Fri, 06 Jan 2023 00:03:34 GMT

hi bergen288

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

Re: Update weekday title dynamically

johnt75 — Fri, 06 Jan 2023 09:23:52 GMT

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

Re: Update weekday title dynamically

bergen288 — Fri, 06 Jan 2023 13:44:40 GMT

great, it works perfectly this time.

Thanks a lot.

Re: Update weekday title dynamically

bergen288 — Fri, 06 Jan 2023 13:46:24 GMT

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