NUMBER OF VISITS

[manideep547]

BELOW IS MY DATA

ID       DATE        DESTINATION           
111      1/12/2019   DESTINATION1               
111      1/12/2019   DESTINATION1               
222      02/12/2019  DESTINATION2              
222      03/12/2019  DESTINATION1              

I Need a number of visits to count  If the same customer is visiting the same destination again on the same day then consider the number of visits to that customer is one.
Number of visits =3
number of visits in destination1 =2
number of visits in destination2 =1

NOTE:  My dataset is very large nearly 54M rows are there.

[az38 · Accepted solution]

Hi @manideep547 

it is exactly thread for Desktop https://community.powerbi.com/t5/Desktop/bd-p/power-bi-designer or DAX https://community.powerbi.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands forums, but try the next technique

First, create a calculated table

Table 2 = 
SUMMARIZE('Table','Table'[DESTINATION],'Table'[DATE],'Table'[ID],"N visits",COUNTROWS('Table'))

or you can do the same in the Power Query Editor mode using Group By functionality

Second, creare a measure in your table

Measure = 
CALCULATE(COUNT('Table 2'[ID]),ALLEXCEPT('Table 2','Table 2'[DESTINATION]))

and you will be able to get desired output

 

[manideep547]

@az38  can you please attach the .pdix file and Thank you for your reply.

[az38]

@manideep547 

try here 

https://ufile.io/8roxj9tj