cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
_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
Highlighted
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
Highlighted
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!!!

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 317 members 3,308 guests
Please welcome our newest community members: