I've faced this challenge before in Qlik and have had to reaggreagte my data in the data model to get it work.
However this time, due to the volumes of data I'm working with I'm unable to do this.
I've have never found a workaround in set analysis and was wondering anyone can help. By the way I have seen this done in PowerBi DAX Expression.
Challenge:
My customer has 600 locations (all this data sits in the locations table) I have then joined the data back onto itself and then used geo mapping to work out the distance from each location to the other locations.
I then store this QVD as 'Alternative Locations'. My data model is linked in the following way, Fact > Location and then Alternative Location joins onto Location using the location Code, Acting as a 1 to many.
In my table, I present the dimensions as Location Name, Alternative Location Name
and measures as:
Sales for Location, but I'm unable to get the alternative locations sales as the link in the fact is through sales.
I could link the alternative table into the fact however, I'd have the same issue as I'd be unable to get to get alternative locations sales.
I'd like to see what the sales are for my primary locations and what the sales are then for each of the other locations within a set radius. This new measure will be used for further set analysis to work out if Stock should be moved from one location to another and which location has the best opportunity to move stock to.
