cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AJP
Regular Visitor

Annotation using a FactEvent Table? Is this Possible?

Hi All,

 

I am looking to annotate a line graph - I have been able to do this using the EVENT & NOTE Dax as detailed in a number of posts, however this doesn't quite give me the functionality that I need. 

 

EVENT = IF(LEN([NOTE]),[TOTAL METRIC],BLANK())

 

Note = SWITCH(
max(DimDate[Date]),
VALUE("01/01/2017"),"PUT TEXT HERE",
BLANK()
)

 

 

 

The NOTE is hardcoded to text events. 

 

Am I able to use a FactEvent table to link to the data and make the notes dynamic and appear in the graph on the specific date? This is much easier to maintain plus we have a lot of events! 

 

Any help much appreciated. 

Thanks


Annie

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft v-jiascu-msft
Microsoft

Re: Annotation using a FactEvent Table? Is this Possible?

Hi Annie,

 

If you have an Event table, you can establish relationship with the DimDate. Then add it to the Tooltip. Please check out the demo here.

The measures could be:

Note =
SWITCH (
    MIN ( 'DimDate'[Date] ),
    MIN ( 'DimDate'[Date] ), MIN ( 'Event'[Event] ),
    BLANK ()
)
Event =
IF ( LEN ( [Note] ) > 0, SUM ( FactEvent[Value] ), BLANK () )

nnotation_using_a_Fact_Event_Table

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
AJP
Regular Visitor

Annotate Line Graph Using FactEvent table. Is this possible?

Hi All,

 

I am looking to annotate a line graph - I have been able to do this using the EVENT & NOTE Dax as detailed in a number of posts, however this doesn't quite give me the functionality that I need. 

 

EVENT = IF(LEN([NOTE]),[TOTAL METRIC],BLANK())

 

Note = SWITCH(
max(DimDate[Date]),
VALUE("01/01/2017"),"PUT TEXT HERE",
BLANK()
)

 

 

 

The NOTE is hardcoded to text events. 

 

Am I able to use a FactEvent table to link to the data and make the notes dynamic and appear in the graph on the specific date? This is much easier to maintain plus we have a lot of events! 

 

Any help much appreciated. 

Thanks


Annie

 

 

 

Microsoft v-jiascu-msft
Microsoft

Re: Annotation using a FactEvent Table? Is this Possible?

Hi @AJP,

 

Could you please provide more details? Please share a dummy sample. I can't see any connection between EVENT and Note. Do you want to put them as tips in a visual?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AJP
Regular Visitor

Re: Annotation using a FactEvent Table? Is this Possible?

Hi Dale, 

 

Thank you for replying. I have tried to add a screenshot but I can't do it - Sorry!

The note looks at the date table and line graph and then pulls back any associated text for that date. 

So in my line graph on the 1st Jan 2017 there is a note along side the value in the hover-over tool tip that states EVENT 1. You can change the line marker to highlight that an event took place on that date.

(I followed this - https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Annotating-Specific-Events-in-Line-Chart...)

 

Note = SWITCH(
max(DimDate[Date]),
VALUE("01/01/2017"),"EVENT 1",
BLANK()
)

 

The Event basicially sees if there is a note (LEN bigger than 0) and brings back the note text which sits in the tool tip. 

 

My issue is I want to record a number of events so using the note DAX i would have to do the following all hard coded

 

Note = SWITCH(
max(DimDate[Date]),
VALUE("01/01/2017"),"",

VALUE("02/01/2017"),"EVENT 2",

VALUE("03/01/2017"),"EVENT 3",

VALUE("04/01/2017"),"EVENT 4",

VALUE("05/01/2017"),"EVENT 5",

BLANK()
)

 

Do you know if it is possible to replace the VALUE("01/01/2017"),"EVENT 1", with an event table like below

 

Date                                  Event

01/01/2017                     EVENT 1

02/01/2017                     EVENT 2

03/01/2017                     EVENT 3

04/01/2017                     EVENT 4

05/01/2017                     EVENT 5

 

So essentially a reference table rather than hard coded text?

I am not very good at explaining things technically, but i hope this makes it a little clearer

 

Thanks

 

Annie

 

 

Highlighted
Microsoft v-jiascu-msft
Microsoft

Re: Annotation using a FactEvent Table? Is this Possible?

Hi Annie,

 

If you have an Event table, you can establish relationship with the DimDate. Then add it to the Tooltip. Please check out the demo here.

The measures could be:

Note =
SWITCH (
    MIN ( 'DimDate'[Date] ),
    MIN ( 'DimDate'[Date] ), MIN ( 'Event'[Event] ),
    BLANK ()
)
Event =
IF ( LEN ( [Note] ) > 0, SUM ( FactEvent[Value] ), BLANK () )

nnotation_using_a_Fact_Event_Table

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

AJP
Regular Visitor

Re: Annotation using a FactEvent Table? Is this Possible?

Dale - that works perfectly! Thank you! 🙂

wmeyer Helper III
Helper III

Re: Annotation using a FactEvent Table? Is this Possible?

Dale,

I have a matrix visual on my page, that I am using to filter my line chart. If I click on an item name, the line chart shows filters to the revenue for that item only. I want the chart to also filter to only show the event and note for that item as well.  How would you adjust the Note or Event measures to work for this? event.PNGic item through the matrix? 

Microsoft v-jiascu-msft
Microsoft

Re: Annotation using a FactEvent Table? Is this Possible?

Hi @wmeyer,

 

I would suggest you create a new post in this forum.

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
antoniopare1985
New Member

Re: Annotation using a FactEvent Table? Is this Possible?

Did you solve this problem?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors