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
_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
Community Champion
Community Champion

@_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
Community Champion
Community Champion

@_Kristina_

 

May be

 

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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