Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sse
Regular Visitor

Last country visited timeline logic

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? 

 

power.png

2 ACCEPTED SOLUTIONS
cs_skit
Resolver IV
Resolver IV

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

 

 

View solution in original post

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
cs_skit
Resolver IV
Resolver IV

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.