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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrpowrbihelp
Frequent Visitor

last visit

Hi,

 

I have a table that looks like this:

Account #     Visit Date     Items Carried                 Contact Name

124                1/8/16            Apples                            Mark

124                5/6/17            Blank                               Alan

124                8/4/17            Blank                              Jacob

125                7/1/17            Oranges                         Patrick

125                9/8/17            Oranges, Pears                Mike

 

I want to be able to see the last visit date, who they spoke to that day, and the last Items they carried.

 

Account #     Last Visit Date     Items Carried          Contact Name

124                8/4/17                 Apples                      Jacob

125                9/8/17                 Oranges, Pears          Mike

 

So even though the Last Vist for Account 124 was 8/4/17, the Items Carried column is not filled in so i need it to go back to the last time it was.

 

3 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

Hi @mrpowrbihelp

 

Use this Measure to get Last Item Carried

Last_Item_Carried =
LASTNONBLANK (
    CALCULATETABLE (
        ALL ( Table1[Items Carried] ),
        FILTER ( Table1, Table1[Account #] = SELECTEDVALUE ( Table1[Account #] ) )
    ),
    CALCULATE ( VALUES ( Table1[Items Carried] ) )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

HI @mrpowrbihelp

 

These MEASURES to get the LAST VISIT DATE and the RELATED CONTACT NAME

 

Last_Visit_Date =
MAX ( Table1[Visit Date ] )

 

Last_Contact_Name =
CALCULATE (
    SELECTEDVALUE ( Table1[Contact Name] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Account #] = SELECTEDVALUE ( Table1[Account #] )
            && Table1[Visit Date ] = MAX ( Table1[Visit Date ] )
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

3 measures:

 

MaxDate = MAX(Items[Visit Date])

MaxContact = MAXX(FILTER(Items,Items[Visit Date]=[MaxDate]),[Contact Name])

MaxItems = MAXX(FILTER(Items,Items[Visit Date]=[MaxDate]),[Items Carried])

Put these into a table visualization with Account #


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Zubair_Muhammad
Community Champion
Community Champion

Hi @mrpowrbihelp

 

Use this Measure to get Last Item Carried

Last_Item_Carried =
LASTNONBLANK (
    CALCULATETABLE (
        ALL ( Table1[Items Carried] ),
        FILTER ( Table1, Table1[Account #] = SELECTEDVALUE ( Table1[Account #] ) )
    ),
    CALCULATE ( VALUES ( Table1[Items Carried] ) )
)

Regards
Zubair

Please try my custom visuals

HI @mrpowrbihelp

 

These MEASURES to get the LAST VISIT DATE and the RELATED CONTACT NAME

 

Last_Visit_Date =
MAX ( Table1[Visit Date ] )

 

Last_Contact_Name =
CALCULATE (
    SELECTEDVALUE ( Table1[Contact Name] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Account #] = SELECTEDVALUE ( Table1[Account #] )
            && Table1[Visit Date ] = MAX ( Table1[Visit Date ] )
    )
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.