Want to add new column in a calculated table to show customer visits by range- Help Needed

[AmeenVanakar]

Hi,

I want to add a new column to show customer visits based on their unique mobile number & unique invoices (i.e. their visits)

Where:

The distinct count of invoice number should match the below condition & show the result in a new column named "visits".

1 = 1 Visit

2 = 2 Visits

3 = 3 Visits

4 = 4 Visits

>=5 = More Than 5 Visits.

I also want to use these values as a filter in the report later, hence I want this in a column format.

I have attached the data snip for you to refer to.

Screenshot 1

I also added a table with values (Screenshot 2) to show the range but I'm not sure how to bring this all together. Any help will be great.

Screenshot 2

Customer data reference:

Customer ID = Customer mobile number

Invoice ID = Bill No

[_AAndrade · Accepted solution]

Hi @AmeenVanakar,

I'm attaching my solution using Power Query.
I hope this can help you.

[Anonymous · Accepted solution]

Hi, @AmeenVanakar 

I've modeled some data to hopefully fit your situation.

Column:

Count = CALCULATE(COUNT('Table'[Unique invoices]),ALLEXCEPT('Table','Table'[Customer mobile number]))

Visit = 
IF([Count]>=5,CALCULATE(MAX(Range[Visits]),FILTER(Range,[Visit Range2]=1000000)),
CALCULATE(MAX(Range[Visits]),FILTER(Range,[Visit Range2]=EARLIER('Table'[Count]))))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[_AAndrade · Accepted solution]

Hi @AmeenVanakar,

I'm attaching my solution using Power Query.
I hope this can help you.