topic Re: Create Mesuare the get the visit id based on entry and exit date in DAX Commands and Tips

Create Mesuare the get the visit id based on entry and exit date

Anupam — Mon, 11 Sep 2023 06:58:52 GMT

HI ,

I have three tables visit data, contain details of customer visit with entry and exit date. A date table (contain all dates) and a customer date with customer date (id and name). I want a table where I can get for each date and each customer the city in which customer was residing on the given date.

For date and customer table, I have done cross join, but I am not able to create a measure to get the visit id corresponding to date and customer value. PFA the demo pbix file.
https://drive.google.com/file/d/11gnlxWPg9hpYtBZr5bPJJd4ZeZWZSkc-/view?usp=sharing

Re: Create Mesuare the get the visit id based on entry and exit date

nirali_arora — Mon, 11 Sep 2023 07:56:07 GMT

This problem seems similar to the HR Analytics problem which has start date and end date.

You can follow the video and find your solution.

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI: HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Re: Create Mesuare the get the visit id based on entry and exit date

ERD — Mon, 11 Sep 2023 08:08:31 GMT

Hi, a measure might be

VisitID = VAR selected_date = MAX ( Customer_date[Date] ) VAR customerid = MAX ( Customer_date[id] ) RETURN CALCULATE ( MAX ( VisitData[City] ), VisitData[Customer] = customerid, VisitData[Entry Date] <= selected_date, VisitData[Exit Date] > selected_date )

Re: Create Mesuare the get the visit id based on entry and exit date

DataNinja777 — Mon, 11 Sep 2023 09:37:05 GMT

Hi Anupam,

I downloaded your file and noted that in the file a relationship was created between the transaction ID in the fact table 'VisitData' and the customer ID in the customer dimension table, so I changed that relationship to be between customer ID and customer # in the 'VisitData', as I thought you meant to create that relationship instead.

The resultant relationship in the data model looks like below:

Then, I created a measures like below to identify the trip locations and ID visited by customers at a given date.

I've then combined these measures in a table measure like below. As you can see, when the customer ID #1 travels from Indore to Mumbai on May 1, 2023, both travel ID #1 and #11 apear on the row of that date with two location names.

I attach the pbix file below:

Travel location ID.pbix