I am trying to create a line chart with MonthYear and Food as dimension, and a measure to get total no shops with in the country where the Food belongs to. below is the sample data. I want to do this in front end, as i cant duplicate the data in backed due to data volume.
Load * Inline [ Country, Food, Qty, Shop, MonthYear, Type IND, Apple, 100, S1, 012021, A IND, Apple, 100, S1, 012021, B IND, Mango, 200, S2, 012021, A ESP, Orange, 400, S3, 012021, C ESP, Grapes, 500, S4, 012021, E ESP, Banana, 200, S5, 012021, D ];
Exactly that is the challenge, its possible that "Apple" is available in "ESP" for another "shop 4" in that case the count will become "5" for Apple as there is total of 5 distinct shops part of IND and ESP.
I have fixed this in backend but the solution increased the app size tremendously due to cross joins.