Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |