cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
msingh2019 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User
Super User

Re: Need help with data transformation in Power BI Desktop

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

Super User
Super User

Re: Need help with data transformation in Power BI Desktop

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
EtienneOL Member
Member

Re: Need help with data transformation in Power BI Desktop

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 ? 

msingh2019 Frequent Visitor
Frequent Visitor

Re: Need help with data transformation in Power BI Desktop

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

msingh2019 Frequent Visitor
Frequent Visitor

Re: Need help with data transformation in Power BI Desktop

Anybody who can help please?

Anonymous
Not applicable

Re: Need help with data transformation in Power BI Desktop

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

Super User
Super User

Re: Need help with data transformation in Power BI Desktop

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

Anonymous
Not applicable

Re: Need help with data transformation in Power BI Desktop


@Ashish_Mathur wrote:

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


@Ashish_Mathur   Good answer Ashish. 

Super User
Super User

Re: Need help with data transformation in Power BI Desktop

Thank you.


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

Re: Need help with data transformation in Power BI Desktop

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.

Super User
Super User

Re: Need help with data transformation in Power BI Desktop

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,106)