Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi! I am struggling to figure this out and would appreciate some help.
I am trying to make a PowerBi report that shows "Which country did people vist before they came to their current country". The user of the dashboard would typically select a country and see the previous country visited. The end product should be able to show all previous countries visited but as a starter the previous country would suffice.
I can easily build this for each person, but I am struggling to create an aggregated view. E.g., when I select Germany I can see that 500, 300 and 250 people visited France, UK and USA before visiting Germany.
Any tips?
Solved! Go to Solution.
this was a pretty tough one for me thanks for that was fun
created calculated column
VisitOrder = COUNTROWS
(
FILTER(countryvisits;EARLIER(countryvisits[Person])= countryvisits[Person] && EARLIER(countryvisits[DateCol])>countryvisits[DateCol])
)
+1
created second calculated column
LastCountry = CONCATENATEX
(
FILTER(countryvisits;EARLIER(countryvisits[Person])=countryvisits[Person]&&EARLIER(countryvisits[VisitOrder])-1 = countryvisits[VisitOrder])
;countryvisits[Country]
)
result
Hi @sse,
You can try to use below formula to get the result table if it suitable for your requirement.
Table formula:
Person Country Records = ADDCOLUMNS( SUMMARIZE(Table,[Person],"Country",LASTNONBLANK(Table[Country],[Country]),"Last Timestamp",LASTTimestamp(Table[Timestamp])), "Previous",LOOKUPVALUE(Table[Country],[Person],[Person],[Timestamp],MAXX(FILTER(ALL(Table),[Person]=EARLIER([Person])&&[Timestamp]<EARLIER([Last Timestamp])),[Timestamp])) )
Regards,
Xiaoxin Sheng
Hi @sse,
You can try to use below formula to get the result table if it suitable for your requirement.
Table formula:
Person Country Records = ADDCOLUMNS( SUMMARIZE(Table,[Person],"Country",LASTNONBLANK(Table[Country],[Country]),"Last Timestamp",LASTTimestamp(Table[Timestamp])), "Previous",LOOKUPVALUE(Table[Country],[Person],[Person],[Timestamp],MAXX(FILTER(ALL(Table),[Person]=EARLIER([Person])&&[Timestamp]<EARLIER([Last Timestamp])),[Timestamp])) )
Regards,
Xiaoxin Sheng
this was a pretty tough one for me thanks for that was fun
created calculated column
VisitOrder = COUNTROWS
(
FILTER(countryvisits;EARLIER(countryvisits[Person])= countryvisits[Person] && EARLIER(countryvisits[DateCol])>countryvisits[DateCol])
)
+1
created second calculated column
LastCountry = CONCATENATEX
(
FILTER(countryvisits;EARLIER(countryvisits[Person])=countryvisits[Person]&&EARLIER(countryvisits[VisitOrder])-1 = countryvisits[VisitOrder])
;countryvisits[Country]
)
result
User | Count |
---|---|
98 | |
91 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |