Forum Discussion
Memorable Memberby ID & Last Date
the table has many fields in which 2 are key: ID and Date
one wishes to display the entire row (all fields) for the Last Date of each unique ID
(the last date for each ID will/may differ)
I am able with a Table Visual to create the measure: L Date = LASTDATE(Table,Date) and if one has just the 2 fields of ID and L Date in the visual this works....but not if you add in the other fields of the row - as then the L DATE value just repeats over and over...
So that approach does not work and I am guessing that a FILTER is the way to go but unsure on the syntax and unsure whether this should occur at the visual level or at the data/query level.... I do need to retain the original table of facts.
Greg_Deckler So I have found the solution.
In the original post I explained that one could use a visual table with ID (name) and a measure field (last date function) of Table1 - - and it displayed okay correctly. The visual seems to have inherent grouping. But if you add other fields to the table then more rows display.
So what I did was create that visual with 2 fields - as just explained - and then using the export feature - exported it to the default CSV and immediately imported it into Power BI as a new table named "Data"
I created a join line between Table1 {Name] and Data [Name]
I then can use the New Table feature in Data View to define a new table using DAX - and used this statement:
TableX = FILTER(Table1,
(Table1[NAME]=RELATED(data[NAME]) && (Table1[DATE]=RELATED(Data[Measure]))))This worked. A little slight of hand in using the feature of Table Visual to enable an export....and a little better educated on the use of Filters as I wasn't sure an AND statement was permitted. All's well that ends well.
The key thing about this whole requirement was to return/display the entire record - all fields - I only use 3 in this post but the real world has alot more fields.
12 Replies
- Greg_Deckler
Community Champion
I believe what you need in your measure is an ALLEXCEPT so that you remove all other context except for the [ID] field. So essentially, an ALLEXCEPT(Table,[ID]) in your measure calculation.
https://msdn.microsoft.com/en-us/library/ee634795.aspx
I *think* what you want is:
L Date = CALCULATE(LASTDATE(Table,[Date]),ALLEXCEPT(Table,[ID]))
- CahabaData
I do appreciate the time/effort taken. Your expression, when plugged into the table visual behaves the same as mine.
Which is to say: if just Name and L Date fields are in the visual it is correct, but as soon as you click other fields it expands to more rows and that L Date value just repeats.
I come from databases and can provide the SQL statement for this task....but can't seem to get my mind around the Power BI / DAX approach. Who ever invents a visual DAX development tool or a SQL/DAX translator will have me as their first customer......
- Greg_Deckler
Can you provide some sample data so that I can recreate this?
Hi,
This solution works for me.
I wanted to ask if the same can be done in power query.
Thanks
