cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CahabaData New Contributor
New Contributor

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

 

www.CahabaData.com
1 ACCEPTED SOLUTION

Accepted Solutions
CahabaData New Contributor
New Contributor

Re: by ID & Last Date

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

www.CahabaData.com

View solution in original post

11 REPLIES 11
Super User
Super User

Re: by ID & Last Date

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]))

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

CahabaData New Contributor
New Contributor

Re: by ID & Last Date

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

www.CahabaData.com
Super User
Super User

Re: by ID & Last Date

Can you provide some sample data so that I can recreate this?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

CahabaData New Contributor
New Contributor

Re: by ID & Last Date

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

www.CahabaData.com
Super User
Super User

Re: by ID & Last Date

@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 DateIDField3Measure
6/1/2016 0:00Dan6
6/2/2015 0:00Jim9

 

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

CahabaData New Contributor
New Contributor

Re: by ID & Last Date

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

www.CahabaData.com
CahabaData New Contributor
New Contributor

Re: by ID & Last Date

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.

 

 

www.CahabaData.com
Super User
Super User

Re: by ID & Last Date

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: by ID & Last Date

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"

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,093)