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
Solved! Go to Solution.
May be
Holidays = CONCATENATEX ( SUMMARIZE ( FILTER ( 'MyCalendar', 'MyCalendar'[Holidays] <> BLANK () ), 'MyCalendar'[Holidays] ), 'MyCalendar'[Holidays], "," )
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!
May be
Holidays = CONCATENATEX ( SUMMARIZE ( FILTER ( 'MyCalendar', 'MyCalendar'[Holidays] <> BLANK () ), 'MyCalendar'[Holidays] ), 'MyCalendar'[Holidays], "," )
Great solution - simple and elegant 🙂
Thank you Very Much!!!
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?
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!
User | Count |
---|---|
389 | |
103 | |
65 | |
53 | |
49 |
User | Count |
---|---|
373 | |
121 | |
80 | |
68 | |
57 |