Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
guerciobr
New Member

Line chart marks from different tables

Hello Guys,

 

I need to do a chart which shows if a medication is been effective. To do this i have two tables called Medication and Exams, the Exams table has 3 collums (pacient_id, membrane_thickness, date) which is populated based on the results of an exam. The Medication table has 3 collums too (pacient_id, medication, date). I made a line chart based on the membrane_thickness (Y-axis) and date (X-axis) filtered by pacient_id. And now i need to mark on the line of the chart the date which the pacient use the medication and the name of medication(collum medication of table medication). How can i do it? 

EXAMS_TABLE

pacient_idmembrane_thicknessdata
140001/01/2020
135001/02/2020
145001/03/2020
120001/04/2020
120001/05/2020
142501/06/2020
150001/07/2020
240001/11/2019
360002/03/2018

MEDICATION_TABLE

paciente_id

medicationdate
1Eylia25/06/2020
1Ozurdex16/03/2020
1Luc20/02/2020
1Avastin20/05/2020
2Ozurdex02/11/2019
2

Avastin

09/11/2019
2Luc16/11/2019
3Avastin03/03/2018
3Avastin10/03/2018

 

See below the line chart with the drawn marks.

 

line chart with the marks of medicationline chart with the marks of medication

 

@Greg_Deckler, i put the tables with the data, thanks. I also saw you topic, and may the lookupvalue may work, but i can not do it work. 

 

 

Thanks,

7 REPLIES 7
v-eqin-msft
Community Support
Community Support

Hi @guerciobr

 

Based on my test, your requirement might not be achieved currently. We usually show value in Data Label instead of text,. So workaround is that you could view this in tooltip.

 

According to my understanding ,the value of the Y-axis is membrane_thickness filtered by the same pacient_id and the same date(Year+Month) in both tables, right?

 

You could use the following formula after creating the relationship between two tables based on pacient_id:

 

union table =
CALCULATE (
SUM ( Exams[membrane_thickness] ),

    FILTER (
        Exams,
        Exams[pacient_id] = MIN ( Medication[pacient_id] )
            && FORMAT ( Exams[data], "MMM-YYYY" )
                = FORMAT ( MIN ( Medication[date] ), "MMM-YYYY" )
    )
)

 

 

 

Then create a line chart and drop medication as tooltip like this:

5.PNG7.png

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

 

Best Regards,

Eyelyn Qin

DebbieE
Community Champion
Community Champion

Hi @guerciobr

Ive been having a little look at your data. Thie first think I would do it to make sure you can see what you want as a table

 

It may mean having to do a bit of remodelling and I would definitely include a date table. I would have the membrane thickness in a fact table because its the metric you are looking at against the medication

 

But If you can see what you want for the line chart in a table you will be in a better position

 

Patient 1

 

Eg

Month      membrane Thickness        medication

March      400                                     LUC

April        200

May         300                                     orzudec

 

So I would try for that first before moving to the line chart. I have a feeling you cant at the moment change the line chart label to a descriptor but you may be able to ask for this on the Submit ideas area https://ideas.powerbi.com/ideas/

Hi Debbie,

 

I agree with you about the data model. But the main point is about the relation of the thickness with the medication. For example, you made an exam and your membrane thickness is 800, for the next 4 weeks you take the medication each week. After the 4 weeks, at week 5 you made the exam again and the membrane thickness was 400. So, the line chart has a line which in the Yaxis vary from 800 to 400 and in the Xaxis vary for the week 1 to week 5. How can i mark the day of the medication in the chart, considering that i do not have the membrane thickness in the day of the medication?  So the mark will not be over the line, is it clear?

 

Thanks for the attetion.

Have you tried adding the data to a table visual? its easier to make sure that you are getting the correct results in a table visual before trying to create a line chart or other visual against it

 

In the case of what your trying to do, Overwrite the label with the Medication where that medication has been taken I dont think you can do that with a Line chart.

 

You might try to customise the series if you add in Medication into Series

CustomiseSeries.JPG

 

And create a different colour for each medication. I dont have enough data to see if working but its worth a try. Thi still wouldnt show the medication on the line chart though. I would definitely add it as an idea but to support it you need to be able to produce a table with the date thickness(Metric) and Medication in power BI first

 

I made that customization in the line chart yesterday as you mentioned, and work fine without the name of medication. I joined all the data in a unique table like this

 

PACIENTE_ID MEMBRANE_THICKNESS  MEDICATION  DATE

1400OCT15/06/2019
1426,67LUC18/06/2019
1488,89LUC25/06/2019
1542,22LUC01/07/2019
1604,44LUC08/07/2019
1666,67LUC15/07/2019
1800OCT30/07/2019
1800OCT01/01/2020
1738,6OZURDEX08/01/2020
1615,78OZURDEX22/01/2020
1484,21OZURDEX06/02/2020
1361,4OZURDEX20/02/2020
1300OCT27/02/2020

 

OCT is the exam, if you see all the membrane_thickness between OCTs are calculated to be exactly over the line chart (In the transactional system the membrane_thickness is 0 for OZURDEX, LUC, EYLIA and other medication), but this calculation is not simple i need to do line by line maybe it is not the smarter and simple way. But the result was:

 

Captura de Tela 2020-08-04 às 13.34.43.png

 

Yesterday, i posted this topic looking for a smater and simple way to do it.

Might be worth having a look at some of the custom visuals.

 

The pulse chart is pretty nice but its very much a case of actually clicking on the visual, or running the visual to look at the medications

PulseVisual.JPG

Its a pity the medication markers dont stay up until you click on them but its quite nice. Medication is in Event Description

 

https://www.youtube.com/watch?v=umIEAknY9iU

Greg_Deckler
Super User
Super User

@guerciobr - Are the words the medication names? Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.