We have a business that has six locations. I want to create a measure that tell us the names of the clients who have visited multiple locations. I know that I can create a table and I can see if they have transacted at the locations but I would like a measure that tells me how many clubs have been visited by each customer.
Thanks in advance!
This should be pretty straightforward to do, but the structure of the specific DAX will depend on how you have your data model set up. Can you please post your PBIX file?
Thanks.
Thanks Brian but the file is too big. Any ideas?
The best option would be to cut the data file down in Power Query. I don’t need to see the whole thing to address the DAX question – just a representative sample of records. Best to cut rows than columns, as not to alter the data model.
Alternatively, you can post the data file on Google Drive, Dropbox, OneDrive, etc. then just post the download link on the forum.
Check out the bottom of the link above for ideas on how to create dummy files for the forum
Sam
Who has visited other clubs - stripped down.pbix (1.2 MB)
I was able to strip the file down to the basics and now its small enough. it is attached. thanks all for the help!
Before diving into the DAX, I created a simple star schema data model with lookup tables for clubs and members:
Without the proper model and relationships in place, even getting basic DAX measures to work is difficult. Once I set that up, I created the following measure to count the number of unique clubs visited by each member:
# Clubs Visited =
CALCULATE(
COUNTROWS(
VALUES( 'Sales Data'[Club Code] )
),
FILTER(
'Sales Data',
[Visited] = 1
),
VALUES( 'Members'[Member Code] )
)
I then created a second measure using CONCATENATEX() to list the specific, unique clubs visited by each member:
Clubs Visited List =
CALCULATE(
CONCATENATEX(
VALUES('Sales Data'[Club Code]),
'Sales Data'[Club Code],
", ",
'Sales Data'[Club Code],
ASC
),
FILTER(
'Sales Data',
[Visited] = 1
),
VALUES( 'Members'[Member Code] )
)
Here’s the resulting table:
I hope this is helpful.
Full solution file posted below.
PS: If you have any questions about setting up a proper data model, I strongly recommend going through this course:
This is amazing, thank you very much!
One last question.
If I wanted to add back some of the data that I erased to make the model leaner, how do I do that?
@jmolina If you’ve made these changes in the Power Query editor, it shouldn’t be to hard just delete those last applied steps from each query and you’re done.
You can find the Applied Steps in the Query Settings pane on the right of your screen.
If it’s not there, go to File / Options and Settings / Options
select Power Query Editor and select Display the Query Settings pane
Hope this helps
