Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bergen288
Helper III
Helper III

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"
    )
bergen288_0-1672935238579.png

 

1 ACCEPTED SOLUTION

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

View solution in original post

6 REPLIES 6
johnt75
Super User
Super User

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

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

bergen288_0-1672947957579.png

 

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.

hi @bergen288 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors