Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, need some help on a calculated column to get the column 4 "result - most visited" based on column 1& 3 (name, visited). the result has to be the most repeated value in column 3(visited) for that particular name.
Appreciated for your help
Thanks
Table:
name month visited result - most visited
mike | Jan | LA | Dallas |
mike | Jan | Dallas | Dallas |
mike | Jan | Austin | Dallas |
mike | Feb | Dallas | Dallas |
Dan | march | NJ | NJ |
sam | Jan | NY | LA |
sam | march | LA | LA |
sam | march | LA | LA |
sam | march | Austin | LA |
@Anonymous Here is my approach
First create a calculated column to get the count of visit by name and visited
VisitCount = CALCULATE(COUNT('Table'[Visited]),ALLEXCEPT('Table','Table'[Name],'Table'[Visited]))
Then create a measure to get the name of city
Measure =
VAR _max = CALCULATE(MAX('Table'[VisitCount]),ALLEXCEPT('Table','Table'[Name]))
RETURN CALCULATE(MAX('Table'[Visited]),FILTER(ALLEXCEPT('Table','Table'[Name]),'Table'[VisitCount]=_max))
Would give the solution if I could, this worked for me.
Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |