Need to calculate frequency based on slicer value but need to assign frequency

[huzefa_53]

Need to calculate frequency based on slicer value but need to assign frequency a label based on frequency range table 

my frequency table has range but my frequency is calculated based pos check (check_module) pos check also has check_date which is connected with the calender date table 

I have created calculated columns for different module frequencies and assign the table index value based on frequency range given in table

The problem is that the frequency is calculated for all calender date but i have sliced filter which filter based on pos_check (check_date) i want that frequency should be based on that slicer date 

I have attached the file 

https://drive.google.com/file/d/1w9Kiu9UYrettR-IcK6TqyowG38w1-GmY/view?usp=sharing

[huzefa_53]

Hi @tamerj1  ,

You have created relationship of member_since_date with calender so it will bring those members whose frequecy lies in the table

what i want is that last frequency calculated in MM member engagment table should be dynamic based on filter as you can see that pos check and member is related using member_id and the way i am calculating frequency is count pos checks for every member for specific module and pos check is related with calendar on check date 

so the frequency should be dynamic based on date slicer and site slicer i taught by looking at glance that your solution was right but when in detailed i match the data it is not matching

[tamerj1 · Accepted solution]

Hi @huzefa_53 

Is this what you're looking for? https://we.tl/t-Rv7VOYPejx

[huzefa_53]

The frequency number should be between slicer range and that frequency should be assign index according to the frequency index table

lets say i want frequency of december 2022 so my pos check table should give me 0 frequency because there is not check_module dining entries for any member

[huzefa_53]

Hi @tamerj1  ,

You have created relationship of member_since_date with calender so it will bring those members whose frequecy lies in the table

what i want is that last frequency calculated in MM member engagment table should be dynamic based on filter as you can see that pos check and member is related using member_id and the way i am calculating frequency is count pos checks for every member for specific module and pos check is related with calendar on check date 

so the frequency should be dynamic based on date slicer and site slicer i taught by looking at glance that your solution was right but when in detailed i match the data it is not matching

[CNENFRNL]

I suggest you improving your data model first before authoring any measures. The quality of data model is of top priority.

1. I don't see any point creating 'MM Member Engagement' table; you can directly apply all those calculated columns to 'MM Members';
2. There's Referential Integrity Violation in the relationship between 'MM Member' and 'POS Check'; these values from 'POS Check'[member id] don't exist in 'MM Members'[member id]
   

[huzefa_53]

yes i got your point but what should i use in particular scenerio to calculate frequency of pos _check member based on check_module column and that frequency should be count using the particular frequency range table in order to get correct result 

for exampe 

never eat at club has frequency zero (now i want count within the slicer range number of member whose check_date is between slicer date and check module = dining and f&b pos  and frequecy with date is equal to zero )

similarly for other ranges as well given in dining freunency index table

[amitchandak]

@huzefa_53 , Are you looking for frequency column on meausre ?

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

[huzefa_53]

Hi the video you mention is segmentation on fix value what if i say i want to how what quantity a customer lies for a particular date range i can't achieve that , let say in october 2022 customer1 has purchase 5 time quantity of 5 item and overall he purchases 15 times 5 Qty  

[huzefa_53]

yes i got your point but what should i use in particular scenerio to calculate frequency of pos _check member based on check_module column and that frequency should be count using the particular frequency range table in order to get correct result 

for exampe 

never eat at club has frequency zero (now i want count within the slicer range number of member whose check_date is between slicer date and check module = dining and f&b pos  and frequecy with date is equal to zero )

similarly for other ranges as well given in dining freunency index table

[huzefa_53]

I have tried both ways column and measure as well i am not getting this that if i create column frequency how it will work between date range slicer