Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am new to Power BI Desktop and unable to figure out how to transform the data in order to achieve the visualization that I finally want to get.
My source data structure contains 4 columns and looks something like this -
PatientID | PatientStatus | ActiveDate | DischargeDate | DroppedDate
---------------------------------------------------------------------------
p234 | Active | 1-Oct-2018 | NULL | NULL
p234 | Discharged | NULL | 12-Dec-2018 | NULL
p234 | Dropped | NULL | NULL | 15-Feb-2019
p235 | Active | 1-Oct-2018 | NULL | NULL
p235 | Discharged | NULL | 12-Dec-2018 | NULL
p235 | Dropped | NULL | NULL | 15-Feb-2019
---------------------------------------------------------------------------
After transforming, the final data structure should look like this -
Status | Month | Count Of Patients
-------------------------------------
Active | Oct-2018 | 2
Discharged | Dec-2018 | 2
Dropped | Feb-2019 | 2
-------------------------------------
The issue I am facing is there is a different date field for each status value.
Ultimately I am trying to create a stacked column chart with the months on the x-axis, status as legend and Count Of Patients as values.
Please help! Thanks!
Megha
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
For patient p236, i changed the dropped date to 18 Jan 2019 and this is the result i got. Download the PBI file from here.
Hope this helps.
Hi @msingh2019, I should be able to do something for you.
Are there always 1 single date by line, and the two other values are always null ?
Is this the format of date you retrieve from your data source ?
Hi,
Thanks for the quick response. After I read your question I just realised I messed up in describing the source data structure. Apologies for the confusion.
I had another look at the source data and understood that the Patient ID field is distinct, therefore only one record per patient. Source data structure below (I removed the Status field as it is irrelevant to avoid further confusion) -
PatientID | ActiveDate | DischargeDate | DroppedDate
---------------------------------------------------------------------------
p234 | 1-Oct-2018 | 12-Dec-2018 | 15-Feb-2019
p235 | 11-Oct-2018 | 5-Jan-2019 | NULL
p236 | 17-Nov-2019 | | 15-Jan-2019 | 3-Feb-2019
---------------------------------------------------------------------------
So yes, it is possible to have 3 non-null values in all the 3 date columns for a single row. For example, a patient could be Active on 1-Oct-2018, then Discharged 12-Dec-2018 and then Dropped 15-Feb-2019.
And after transformation the above data should look like this -
Status | Month | Count of Patients
-------------------------------------
Active | Oct-2018 | 2
Active | Nov-2018 | 1
Discharged | Dec-2018 | 1
Discharged | Jan-2019 | 2
Dropped | Feb-2019 | 2
To your second question, the original date format I receive in the source data is dd/mm/yyyy.
Thanks again!
Megha
Anybody who can help please?
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you @Ashish_Mathur! I tried your PBIX file and it worked perfectly as per my question, so marking your response as solution.
I just have one more scenario that I am struggling with and will be great if you can help with this one as well. There are situations where DischargeDate and DroppedDate happens during the same month, in which case the same patient is counted twice (Discharged and Dropped separately) during the same month. Is there any way to work around this so that the patient is counted only once as dropped (dropped takes precendence)? Will also work if the later/higher date takes precedence. For instance, DischargedDate = 12 Jul 2019 and DroppedDate = 23 Jul 2019. In this scenario this patient should be counted as Dropped in July.
Thanks again.
Hi,
For patient p236, i changed the dropped date to 18 Jan 2019 and this is the result i got. Download the PBI file from here.
Hope this helps.
Hi @Ashish_Mathur, thanks! Really appreciate the help. I got a follow up question but will post it as a separate thread as my original query is now resolved.
You are welcome. Please mark my previous response as Answer.
@Ashish_Mathur wrote:Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur Good answer Ashish.
Thank you.
Hi @msingh2019
What i think best it to create 3 calculated summations by all your dates field.
Link your first date(active date) to Date, the next two dates fields will create two inactive relationships with date table
1. Create a date table and link your active date first.
Patient Table[ActiveDate] = CALCULATE ( Count( PatientID), RELATED ( 'Date'[Date] )), USERELATIONSHIP ( <date table>.date, patient [ActiveDate] ) )
2. Next create
Patient[DischargeDate] = CALCULATE ( Count( PatientID), RELATED ( 'Date'[Date] )), USERELATIONSHIP ( <date table>.date, patient [DischargeDate] ) )
3.similar to the above create droped date.
Patient[DroppedDate] = CALCULATE ( Count( PatientID), RELATED ( 'Date'[Date] )), USERELATIONSHIP ( <date table>.date, patient [DroppedDate] ) )
Filter out the status in the same Dax statement
All the best
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |