Enhancement Request – Add Visit Frequency Count to Employee Location Measure

Hey Swami_om ,

upload a PBIX file to OneDrive or Dropbox, that contains sample data but besides represents your semantic model (realtionships, calculated columns, and measures).

Next to that describe the expected outcome based on the sample data you provided.

Regards,
Tom

Welcome to the forums.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hi Swami_om,

Hope you're doing well!

I agree with lbendlin and TomMartens.

Can you confirm that you want an output loke this ?

New York (5) | Chicago (2) | Austin (1)

If yes, here's a proposition to test it that can maybe helps you :

Visited Cities (Final v2) =
VAR EmployeeEmail =
SELECTEDVALUE('WorkdayBridge'[Email])

VAR OfficeCity =
SELECTEDVALUE('WorkdayBridge'[Office City])

VAR SelectedLocation =
SELECTEDVALUE('WorkdayBridge'[Location])

/* Ensure employee is part of selected location */
VAR IsEmployeeInLocation =
CALCULATE(
COUNTROWS('WorkdayBridge'),
REMOVEFILTERS(NetworkData),
'WorkdayBridge'[Email] = EmployeeEmail,
'WorkdayBridge'[Location] = SelectedLocation
)

/* Check if employee has at least one visit to Office City */
VAR HasOfficeVisit =
CALCULATE(
COUNTROWS(NetworkData),
REMOVEFILTERS(NetworkData),
NetworkData[UserEmail] = EmployeeEmail,
NetworkData[SP.City] = OfficeCity
)

/* Build Office City string with visit count */
VAR OfficeCityWithCount =
OfficeCity & " (" & HasOfficeVisit & ")"

/* Get other visited cities with visit counts */
VAR OtherCities =
CALCULATE(
CONCATENATEX(
ADDCOLUMNS(
DISTINCT(NetworkData[SP.City]),
"@VisitCount",
CALCULATE(
COUNTROWS(NetworkData),
ALLEXCEPT(NetworkData, NetworkData[SP.City])
)
),
NetworkData[SP.City] & " (" & [@VisitCount] & ")",
" | ",
NetworkData[SP.City],
ASC
),
REMOVEFILTERS(NetworkData),
NetworkData[UserEmail] = EmployeeEmail,
NetworkData[SP.City] <> OfficeCity
)

RETURN
IF(
NOT ISBLANK(EmployeeEmail)
&& NOT ISBLANK(OfficeCity)
&& IsEmployeeInLocation > 0,

IF(
HasOfficeVisit > 0,
IF(
ISBLANK(OtherCities),
OfficeCityWithCount,
OfficeCityWithCount & " | " & OtherCities
),
""
),
BLANK()
)