cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
_Kristina_
Advocate I
Advocate I

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
Zubair_Muhammad
Super User
Super User

@_Kristina_

 

May be

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
MarkFinn
Frequent Visitor

Nice one! Can't imagine there'll be a better concatenate for Matrices until MS gets around to adding it as a builtin.
Thanks Zubair!

Zubair_Muhammad
Super User
Super User

@_Kristina_

 

May be

 

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

Regards
Zubair

Please try my custom visuals

 Great solution - simple and elegant 🙂

Thank you Very Much!!!

@Zubair_Muhammad

Thank you! It worked perfectly 🙂

Anonymous
Not applicable

Wow its great, it worked for me as well -  I was sitting with the same problem for couple of days.

But what's the logic here in simple english. Could you explain please - 
How did combining summarize function with concatenatex function avoid the repeating values i.e. Easter?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!