Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
Solved! Go to Solution.
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] ) ) )
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 ] ) ) )
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 #
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] ) ) )
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 ] ) ) )
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!