Forum Discussion
Stuck with DAX - need to replicate Excel formula
If you click 'Transform Data' in the sample file I sent through, you'll notice that I've used your current data collection format and transformed it for you, so up to you if you change the collection method or not - let me know if you still are getting stuck, otherwise hope there's a solution in one of these replies for you 🙂
Community Championmpbems Sorry, that was just meant to be a sample.
Is the data you provided the raw data? Or can you get it in a different format?
If it's the raw data, I would unpivot it first in Power Query - Unpivot the other columns after selecting the Visitor and Week Column.
Then Split column > by non-digit digit to remove the numbers from the days of week.
Finally, use one of my favorite functions, CONCATENATEX:
Visitors =
CONCATENATEX( Table, Table[Value], ", ")
Put that in a matrix with Attribute in the Columns and Week in the rows. Add your slicers for Location.
You can rename Attribute and Value to better names that make sense to you of course. 🙂
At some stage you may consider getting dimension tables, but we'll leave that for another post.
AllisonKennedy I think this is a better representation of what I'm trying to accomplish:
One slicer will include the weeks, while the other will include all locations in a dropdown.
I'm looking to select a week and a location, and view all visitors at that week and location on each day of the week. I know I'll need at least 7 columns but I can't quite get the right formula.
CONCATENATEX sounds like the way to go, but I'm not sure of the right format to use to look at the two slicer selections.
- AllisonKennedy
mpbems The slicers will come automatically - see this file and let me know if it's what you want.
AllisonKennedy this is great! I think I need to revise how my data is collected, since that is probably making this more difficult than it should be.
This is how my data is currently recorded:
Instead of submitting their schedules in one line, I'm going to separate them out so it's one entry per day of week and then I should be able to get the results I'm looking for. Thank you!
- AllisonKennedy
If you click 'Transform Data' in the sample file I sent through, you'll notice that I've used your current data collection format and transformed it for you, so up to you if you change the collection method or not - let me know if you still are getting stuck, otherwise hope there's a solution in one of these replies for you 🙂