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

Highlighted
hiroshi_uchida Frequent Visitor
Frequent Visitor

Re: Concatenate only unique strings in measure

Thank you Very Much!!!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 27 members 1,044 guests
Please welcome our newest community members: