cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
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

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

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?

LW Member
Member

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

Highlighted
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

LW Member
Member

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.

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 122 members 1,772 guests
Please welcome our newest community members: