cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
_Kristina_ Regular Visitor
Regular Visitor

Concatenate only unique strings in measure

Hi everyone,

 

I'm struggeling with a measure I need for a tooltip. In my scenario I have a calendar with important holidays etc, and I'm currently working on a weekly graph. My calendar has one row for each day, and a column named Holidays. I want to concatenate only the unique values from the calendar to this measure so that if I drill down to daily graph there will say "Easter" in the tooltip every day. When you don't drill down and have weeks in the x axis I want it to show "Easter" only ONE time. With my current measure the tooltip shows "Easter" seven times for the week of easter this year. 

 

Example: During easter my calendar would look like this:

 

Date            Holiday

26.03.18       Easter

27.03.18       Easter

28.03.18       Easter

29.03.18       Easter

Etc....

 

My current measure:

Holidays = CONCATENATEX(FILTER('MyCalendar';'MyCalendar'[Holidays] <> BLANK()); 'MyCalendar'[Holidays];",")

 

My current output in tooltip:

Week 13:
Easter, Easter, Easter, Easter, Easter, Easter, Easter

 

My wanted outcome:

Week 13:

Easter

 

By the way: some weeks (and months if you drill up) have several unique Holiday values, the "First function" is thus not what I am looking for.

 

Is there anyone out there who can help out?

 

Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Concatenate only unique strings in measure

@_Kristina_

 

May be

 

Holidays =
CONCATENATEX (
    SUMMARIZE (
        FILTER ( 'MyCalendar', 'MyCalendar'[Holidays] <> BLANK () ),
        'MyCalendar'[Holidays]
    ),
    'MyCalendar'[Holidays],
    ","
)
3 REPLIES 3
Super User
Super User

Re: Concatenate only unique strings in measure

@_Kristina_

 

May be

 

Holidays =
CONCATENATEX (
    SUMMARIZE (
        FILTER ( 'MyCalendar', 'MyCalendar'[Holidays] <> BLANK () ),
        'MyCalendar'[Holidays]
    ),
    'MyCalendar'[Holidays],
    ","
)
_Kristina_ Regular Visitor
Regular Visitor

Re: Concatenate only unique strings in measure

@Zubair_Muhammad

Thank you! It worked perfectly :-)

hiroshi_uchida Frequent Visitor
Frequent Visitor

Re: Concatenate only unique strings in measure

Thank you Very Much!!!