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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
msingh2019
Helper II
Helper II

Need help with data transformation in Power BI Desktop

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

2 ACCEPTED SOLUTIONS

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable


@Ashish_Mathur wrote:

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


@Ashish_Mathur   Good answer Ashish. 

Thank you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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