Forum Discussion
Transfering column data into multiple columns with DAX
Transfer column entries into multiple columns in DAX
Hi- I work for a hospital and I need some DAX help. I am looking to take a column of donation dates and split it into multiple columns, but preserving the same row/column structure. I need to be able to calculate the sum of annual donation totals, but based on the presence/absence of donations in other years. For example, someone who donated in 2015, but not 2016 or 2017. We use these kinds of binary filtering to figure out donors who are lapsed, recaptured, new, etc. I have tried making a number of CALCULATE(SUMX columns using DATESBETWEEN but I can only get one column. Successive attempts return ‘circular dependency’ alerts. I’ve also tried GROUPBY, SUMMARIZE, and simple SUMX(FILTER columns without success. I need columns because calculated measures cannot be used for advanced filtering. Here is how my data is structured now:
Name | Date | Amount | RevenueCenter |
John | 2015 | 100 | Cardiology |
John | 2016 | 100 | Oncology |
John | 2017 | 100 | Neurology |
Joe | 2015 | 100 | Cardiology |
Joe | 2016 | 100 | Oncology |
Joe | 2017 | 100 | Neurology |
Mary | 2015 | 100 | Cardiology |
Mary | 2016 | 100 | Oncology |
Mary | 2017 | 100 | Neurology |
Deb | 2015 | 100 | Cardiology |
Deb | 2016 | 100 | Oncology |
Deb | 2017 | 100 | Neurology |
Here is what I would like it to look like:
Name | Date | Amount | 2015 | 2016 | 2017 | RevenueCenter |
John | 2015 | 100 | 100 |
|
| Cardiology |
John | 2016 | 100 |
| 100 |
| Oncology |
John | 2017 | 100 |
|
| 100 | Neurology |
Joe | 2015 | 100 | 100 |
|
| Cardiology |
Joe | 2016 | 100 |
| 100 |
| Oncology |
Joe | 2017 | 100 |
|
| 100 | Neurology |
Mary | 2015 | 100 | 100 |
|
| Cardiology |
Mary | 2016 | 100 |
| 100 |
| Oncology |
Mary | 2017 | 100 |
|
| 100 | Neurology |
Deb | 2015 | 100 | 100 |
|
| Cardiology |
Deb | 2016 | 100 |
| 100 |
| Oncology |
Deb | 2017 | 100 |
|
| 100 | Neurology |
Thank you for much!
4 Replies
Hi npatten
Yes it's possible to manipulate the data as you suggest in both DAX and the Query Editor, but I suggest you keen the data as is and use another method to derive the Lapsed, Recaptured flags for reporting.
If you'd like help with that then let me know, thought your test dataset might need to mix it up a little to show difference results working correctly.
Phil_SeamarkThank you for your offer of help. I would certainly like to discuss your ideas. What did you have in mind?
Thanks!
I guess just a bit more sample data in the orignal format but mix up the numbers dates and values a bit so we can generate some DAX to return the results you need.
