by ID & Last Date

[CahabaData]

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]

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?

[CahabaData]

DATE       ID        Field3    Field4   Field5 etc
1/1/2015 Dan       6
2/1/2015 Jim        5
6/1/2015 Dan       3
6/2/2015 Jim        9
1/1/2016 Dan       2
6/1/2016 Dan       1

result should be: (full record of last date per ID)

DATE        ID     Field3   Field4  Field5  etc
6/2/2015 Jim      9
6/1/2016 Dan     1

in real world there are of course values in the other fields - this is the bare minimum needed I believe to test the logic.  Because I expect to want/need to use the Result table for slicing - I am thinking this is to be made in the data model at the table level....but that may be just because I am use to thinking in terms of tables & queries being established to serve as record sources for reports.......

[Greg_Deckler]

@CahabaData I think this is what you need.

OK, here is my query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDRV0lFyScwDkmZKsTrRSkYIYa/MXCBpChY2Q1dtDBU2QlVtCRaGmm0GV22EbAhC2FApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DATE = _t, ID = _t, FIELD3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"ID", type text}, {"FIELD3", Int64.Type}})
in
    #"Changed Type"

Created the following measures:

L Date = CALCULATE(LASTDATE('LastDate'[DATE]),ALLEXCEPT('LastDate','LastDate'[ID]))

Field3Measure = MAX([FIELD3])

Add "L Date", ID and "Field3Measure" to a table to get:

L Date	ID	Field3Measure
6/1/2016 0:00	Dan	6
6/2/2015 0:00	Jim	9

Why this works. L Date with the ALLEXCEPT clause ensures that calculation of the LASTDATE is not affected by anything other then the ID filter context. Creating a Measure for Field3 enables the calculation of that value to be context filtered by applicable filters, in this case "L Date" within the table.

[CahabaData]

ay carumba!! .... @Greg_Deckler  ...... first let me thank you for your time....

what in the world is the LET and IN code? that's way beyond my know-how....

so there is a conceptual problem with Field3 in your solution- Field3 is just a field like 4, 5, etc. It can't be defined via a measure (and also doesn't = Max)... and they all must exist in the result table/visual.

In SQL, I would do this:
*. Make an Aggregate query that groups on Name, and is set for Max(Date). This would result on this record set with 2 columns and 2 records (one for each person with their max Date). I would save this as Ag Query.
*. Make a second query using the core fact table with a double join to Ag Query on both Name and Date fields
A join acts much like a filter in that what is returned must exist on both sides. In this example I want all the fields of the core fact table but just the rows of the Ag Query (in this data set that is 2). This would be the statement:

SELECT Table1.Date, Table1.ID, Table1.Field3, Table1.Field4
FROM Table1 INNER JOIN AgQuery ON (Table1.Date = AgQuery.MaxOfDate) AND (Table1.ID = AgQuery.ID)

....am going thru some DAX info trying to educate myself although probably will just go watch some baseball soon.....

[Greg_Deckler]

Let me read through this more thoroughly and get back to you. The let and in statement is the query I created. In the Query Editor, go to View | Advanced Editor and you will see the query code. This is Power Query "M" code. You can copy and paste my code into your own blank query and recreate what I did. 

You might be better off solving this via Power Query "M" code, I'll have to take a look at that.

[Greg_Deckler]

This M code gives me the right answer, but it is only because your 6 and 9 are both maximums. Might steer you down the right path however:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDRV0lFyScwDkmZKsTrRSkYIYa/MXCBpChY2Q1dtDBU2QlVtCRaGmm0GV22EbAhC2FApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DATE = _t, ID = _t, FIELD3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"ID", type text}, {"FIELD3", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Date", each List.Max([DATE]), type date}, {"Field3", each List.Max([FIELD3]), type number}})
in
    #"Grouped Rows"

[CahabaData · Accepted solution]

@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. 

[CahabaData]

again, your time is appreciated...

the values (6,9, etc) are just another field in the record and not part of the criteria in selecting/filtering the rows - - it is the Max Date per ID that is the criteria... but one assumes that changing from max value to max date is do-able so it is definitely the right idea....

am trying to better understand the filtering in regard to what I think is an AND statement for Unique ID AND Max(Date) - - but haven't got a handle on it yet.

if it must be accomplished in M code then so be it - am learning the boundaries of what Power BI offers - though M is entirely new to me and will need to do some learning....

[CahabaData]

I forgot to display the SQL statement for Ag Query:

SELECT Table1.ID, Max(Table1.Date) AS MaxOfDate
FROM Table1
GROUP BY Table1.ID

One could nest this inside the other statement and I believe in DAX one must - with SQL I leave them separate so that I can fire/test the Ag Query stand alone to be sure it returns the results I seek....but that's just me.