Repeat Records - How to make the query more effective?

[Anonymous]

Hi everyone

I am try to get how many days that each customer visit the same location during a given period/range.

Below is the query I used to create a new column:

Repeat_Visits =

VAR __location_Name__ = 'data'[Location Name]

VAR __CID__ = 'data'[Customer_ID]

RETURN

CALCULATE(DISTINCTCOUNT('data'[Date]),FILTER('Data','Data'[Customer_ID]=__CID__),FILTER('data','data'[Location Name]=__location_Name__))

This query works but takes 10 minutes to complete.

Is there any similar query more effective and can return the same result ("Repeat Purchases at the same location") faster?

Below are the sample data (first table) and expected result (second table).

Thank you in advance!

[Anonymous]

Thanks everyone!

The reason I am using this complex query is to create a new column and use it as a fliter or to compute.

Thus, I do not think I can use measure here.

[Ashish_Mathur]

Hi,

This is confusing.  Why would my measure not work?

[Anonymous]

Hi  @Ashish_Mathur 

Don't get me wrong. Your measure work perfectly funtionally. 

Yet, I feel like I need a column for a filter in my case, and I guess we are not allowed to use measure as a filter yet, right?

Thanks again.

[v-kelly-msft]

Hi @Anonymous ，

Just use a measure below:

Measure = COUNT('Table'[Date])

Then drag Customer_ID and Location_Name to the Table visual with the measure above and you will see:

Part of your sample:                                                              

  The result is :

      

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

Drag Customer_ID and Location_Name to the Table visual and write this measure

=COUNTROWS(Data)

Hope this helps.

[amitchandak]

I think simple distinctcount will do it.

What is the advantage other var are adding?

Distinct count = CALCULATE(DISTINCTCOUNT('data'[Date]))

And plot it against customer and location in visual.

If you need a kind of subtotals. try ALLEXCEPT

https://community.powerbi.com/t5/Desktop/ALLEXCEPT-in-a-multitable-setup/td-p/403205

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin