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,
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 ] ) ) )
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |